MySQL Stored Procedures

Introduction

Stored procedures in MySQL are pre-compiled and stored database objects that allow you to encapsulate a sequence of SQL statements and execute them as a single unit. They provide reusability, modularity, and enhanced security in database operations. In this article, we will explore the usage of stored procedures in MySQL with examples to illustrate their functionality.

Creating a Stored Procedure

To create a stored procedure in MySQL, you can use the CREATE PROCEDURE statement. Here's an example of creating a simple stored procedure that retrieves employee details:

CREATE PROCEDURE GetEmployeeDetails()
BEGIN
    SELECT * FROM employees;
END;

Executing a Stored Procedure

To execute a stored procedure, you can use the CALL statement followed by the procedure name. Here's an example of executing the "GetEmployeeDetails" stored procedure:

CALL GetEmployeeDetails();

Passing Parameters to a Stored Procedure

You can define parameters in a stored procedure to make it more flexible. Here's an example of creating a stored procedure that retrieves employee details based on a department ID:

CREATE PROCEDURE GetEmployeeByDepartment(IN dept_id INT)
BEGIN
    SELECT * FROM employees WHERE department_id = dept_id;
END;

To execute the stored procedure with a parameter, you need to pass the parameter value when calling the procedure:

CALL GetEmployeeByDepartment(1);

Modifying a Stored Procedure

If you need to modify a stored procedure, you can use the ALTER PROCEDURE statement. Here's an example of modifying the "GetEmployeeDetails" stored procedure to include an additional condition:

ALTER PROCEDURE GetEmployeeDetails()
BEGIN
    SELECT * FROM employees WHERE salary > 50000;
END;

Dropping a Stored Procedure

To remove a stored procedure from the database, you can use the DROP PROCEDURE statement. Here's an example of dropping the "GetEmployeeDetails" stored procedure:

DROP PROCEDURE GetEmployeeDetails;

Conclusion

Stored procedures in MySQL offer a powerful way to encapsulate and execute a sequence of SQL statements as a single unit. In this article, we explored the creation, execution, parameter passing, modification, and removal of stored procedures in MySQL. By utilizing stored procedures, you can enhance the reusability, modularity, and security of your database operations, making your MySQL databases more efficient and maintainable.