MySQL Stored Procedures

A stored procedure is a set of SQL statements that can be executed on the database server. These procedures are stored in the database and can be invoked by applications or users. Stored procedures provide advantages such as improving performance, reusability, and security by encapsulating logic on the server side.

Key Benefits of Using Stored Procedures:

• Performance: Stored procedures are precompiled and stored in the database, reducing the overhead of query parsing and optimizing execution speed.
• Reusability: Once written, stored procedures can be reused by different applications, reducing redundancy.
• Security: Stored procedures allow users to execute specific functions without directly accessing the underlying data, offering better control over database access.
• Maintainability: Logic is centralized in the database, making updates and changes easier and more consistent.

Example of a MySQL Stored Procedure

CREATE PROCEDURE AddEmployee(
    IN emp_name VARCHAR(100),
    IN emp_position VARCHAR(50),
    IN emp_salary DECIMAL(10, 2)
)
BEGIN
    INSERT INTO employees (name, position, salary)
    VALUES (emp_name, emp_position, emp_salary);
END //

• The procedure is named AddEmployee and takes three input parameters: emp_name, emp_position, and emp_salary.
• The procedure inserts a new record into the employees table using the provided values.
• The DELIMITER command is used to change the statement delimiter to // so that the entire procedure can be defined in a single block.

Calling the Stored Procedure

CALL AddEmployee('John Doe', 'Manager', 60000.00);
  

This will insert a new employee named “John Doe” with the position of “Manager” and a salary of 60000 into the employees table.