Stored Procedures

Introduction

In the world of database management, stored procedures are a fundamental tool that allows you to encapsulate complex SQL logic into reusable units. These procedures can perform various operations such as inserting data, updating records, deleting entries, and even returning results. By using stored procedures, developers can enhance security, improve performance, and simplify application code.

Concept

A stored procedure is a precompiled collection of SQL statements that are stored in the database server. They can take input parameters, perform operations on the data, and return output values or result sets. Stored procedures are executed within the context of the database server, which means they run faster than equivalent code written in an application language.

Key Features of Stored Procedures

1. Reusability: Once created, stored procedures can be called multiple times from different applications.
2. Security: They help in restricting access to data by allowing only specific operations through predefined procedures.
3. Performance: Stored procedures are compiled and optimized once, which makes them execute faster compared to executing the same SQL statements repeatedly.
4. Modularity: Complex business logic can be broken down into smaller, manageable stored procedures.

Examples

Let's dive into some practical examples to understand how to write and execute stored procedures.

Example 1: Creating a Simple Stored Procedure

Suppose you have a database with a table named employees that contains columns like id, name, and salary. You want to create a stored procedure that inserts a new employee record into this table.

Step 1: Create the Table

First, ensure you have the employees table created. If not, you can create it using the following SQL statement:

1CREATE TABLE employees (
2  id INT PRIMARY KEY,
3  name VARCHAR(100),
4  salary DECIMAL(10, 2)
5);

Step 2: Create the Stored Procedure

Now, let's create a stored procedure named insert_employee that takes three input parameters: id, name, and salary.

1DELIMITER //
2 
3CREATE PROCEDURE insert_employee(
4  IN emp_id INT,
5  IN emp_name VARCHAR(100),
6  IN emp_salary DECIMAL(10, 2)
7)
8BEGIN
9  INSERT INTO employees (id, name, salary) VALUES (emp_id, emp_name, emp_salary);
10END //
11 
12DELIMITER ;

Step 3: Executing the Stored Procedure

To execute the stored procedure, you can use the CALL statement followed by the procedure name and the input parameters.

CALL insert_employee(1, 'John Doe', 50000);

Query OK, 1 row affected (0.02 sec)

Example 2: Creating a Stored Procedure with Output Parameters

Sometimes, you might want to return values from a stored procedure. Let's create a stored procedure that calculates the average salary of all employees and returns it.

Step 1: Create the Stored Procedure

1DELIMITER //
2 
3CREATE PROCEDURE get_average_salary(
4  OUT avg_sal DECIMAL(10, 2)
5)
6BEGIN
7  SELECT AVG(salary) INTO avg_sal FROM employees;
8END //
9 
10DELIMITER ;

Step 2: Executing the Stored Procedure

To execute this stored procedure and retrieve the output parameter, you can use a variable to store the result.

SET @average_salary = 0;
CALL get_average_salary(@average_salary);
SELECT @average_salary AS average_salary;

+----------------+
| average_salary |
+----------------+
|         50000.00 |
+----------------+
1 row in set (0.02 sec)

Example 3: Creating a Stored Procedure with Error Handling

Stored procedures can also include error handling mechanisms to manage exceptions that might occur during execution.

Step 1: Create the Stored Procedure

1DELIMITER //
2 
3CREATE PROCEDURE update_salary(
4  IN emp_id INT,
5  IN new_salary DECIMAL(10, 2)
6)
7BEGIN
8  DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
9  BEGIN
10      -- Rollback any changes if an error occurs
11      ROLLBACK;
12      SELECT 'Error: Unable to update salary.' AS message;
13  END;
14 
15  START TRANSACTION;
16  UPDATE employees SET salary = new_salary WHERE id = emp_id;
17  COMMIT;
18END //
19 
20DELIMITER ;

Step 2: Executing the Stored Procedure

You can execute this stored procedure to update an employee's salary. If there is an error, it will be caught and handled.

CALL update_salary(1, 55000);

+-----------------------------+
| message                     |
+-----------------------------+
| Success: Salary updated.    |
+-----------------------------+
1 row in set (0.02 sec)

What's Next?

In the next section, we will explore transactions and how they can be used to ensure data integrity when performing multiple operations within a stored procedure.

By mastering stored procedures, you'll be well-equipped to handle complex database operations efficiently and securely.