Course Content
MYSQL Tutorial
About Lesson

Creating and Using Stored Procedures

In the realm of relational databases, MySQL stands as a prominent player, and mastering its features can greatly enhance your database management skills. One such powerful feature is the use of stored procedures. In this post, we’ll delve into the creation and utilization of stored procedures in MySQL, exploring their benefits and providing practical examples.

What Are Stored Procedures?

Stored procedures are precompiled SQL statements that are stored in the database. They allow you to group a set of SQL statements into a single unit, which can be executed with a single call. This not only simplifies code management but also enhances security and performance.

Advantages of Using Stored Procedures

  1. Modularity and Reusability: Stored procedures promote modular programming by allowing you to encapsulate complex logic. Once created, they can be reused in multiple parts of your application.

  2. Improved Performance: Stored procedures are precompiled and stored in a compiled form, reducing parsing and optimization time during execution. This results in faster query performance.

  3. Enhanced Security: By using stored procedures, you can control access to the data at a more granular level. Users can be granted permission to execute a stored procedure without needing direct access to the underlying tables.

Creating Stored Procedures

Let’s walk through the basic steps of creating a simple stored procedure.

DELIMITER //

CREATE PROCEDURE GetEmployeeDetails(IN employee_id INT)
BEGIN
SELECT * FROM employees WHERE id = employee_id;
END //

DELIMITER ;

In this example, we’ve created a stored procedure  GetEmployeeDetails that takes an employee ID as input and returns the details of the corresponding employee.

Executing Stored Procedures

Executing a stored procedure is straightforward. Use the CALL statement followed by the procedure name and its parameters.

CALL GetEmployeeDetails(101);

Parameters in Stored Procedures

Stored procedures can accept input and output parameters, allowing for dynamic behavior. Parameters are specified in the CREATE PROCEDURE statement and used within the procedure body.

CREATE PROCEDURE UpdateSalary(IN employee_id INT, IN new_salary DECIMAL(10, 2))
BEGIN
UPDATE employees SET salary = new_salary WHERE id = employee_id;
END;