🔍

Guide to MySQL Stored Procedures

Oct 16, 2024

Lecture Notes: Understanding Stored Procedures in MySQL

Introduction to Stored Procedures

  • Definition: Stored procedures are prepared SQL code that can be saved and reused.
  • Use Case: Useful for queries that are frequently written, reducing repetition and potential errors.
  • Example: A verbose SQL statement interjoining transactions and customers tables to display customers who made transactions.

Creating a Simple Stored Procedure

  • Syntax Overview:
    • Use CREATE PROCEDURE followed by the procedure name.
    • Enclose statements within BEGIN and END keywords.
  • Delimiter Issue:
    • MySQL uses semicolons as delimiters, indicating the end of a statement.
    • Temporarily change the delimiter to something else (e.g., $$) when creating procedures and revert it back afterwards.
  • Example Procedure:
    • Procedure Name: getCustomers.
    • Selects all entries from the customers table.
  • Invocation: Use CALL followed by the procedure name and parentheses.
  • Dropping a Procedure: Use DROP PROCEDURE followed by the procedure name.

Passing Parameters to Stored Procedures

  • Creating a Procedure with Parameters:
    • Use parameters to pass data into procedures.
    • Example: Creating a findCustomer procedure with a customer ID parameter.
    • Set up parameters using IN, a nickname, and data type (e.g., INT).
  • Syntax for Parameters:
    • Example: IN ID INT for a transaction ID.
    • Write SQL statements between BEGIN and END.
    • Change the delimiter when creating the procedure and revert it back.
  • Example Usage:
    • CALL findCustomer(1) to retrieve customer information by ID.

Multiple Parameters Stored Procedures

  • Example: findCustomer using both first name and last name as parameters.
  • Parameter Setup:
    • Use nicknames for parameters (e.g., fname, lname).
    • Specify data types (e.g., VARCHAR(50)).
    • Separate multiple parameters with a comma.
  • Invocation: CALL findCustomer('Larry', 'Lobster') to retrieve detailed customer information.

Benefits and Drawbacks of Stored Procedures

  • Benefits:
    • Reduces network traffic.
    • Increases performance.
    • Enhances security through permission control.
  • Drawbacks:
    • Increases memory usage per connection.

Conclusion

Stored procedures are a powerful MySQL feature for enhancing performance, security, and reducing code repetition, despite the trade-off of increased memory usage per connection.

Full transcript