A stored procedure is a group of pre-compiled SQL statements stored in the database and can be called by the application. This allows for efficient execution of frequently used operations, as the database parses and optimizes the SQL statements when the stored procedure is created rather than each time the application calls it.
Stored procedures can also accept input parameters and return multiple results, making them a powerful tool for data manipulation and management.
Creating Stored Procedures in MySQL
In MySQL, stored procedures are created using the CREATE PROCEDURE
statement. The syntax for this statement is:
CREATE PROCEDURE procedure_name (param1 datatype, param2 datatype, ...)
BEGIN
-- SQL statements
END;
CREATE PROCEDURE
SyntaxFor example, the following stored procedure accepts two input parameters; first_name
and last_name
. The stored procedure also returns a result set containing all the rows in the students
table, where the first_name
and last_name
match the input parameters.
Calling Stored Procedure
Once a stored procedure has been created, it can be called by the application using the CALL
statement. The syntax for this statement is:
CALL procedure_name (param1, param2, ...);
CALL
SyntaxFor example, to call the get_student_info
stored procedure with the first_name
and last_name
input parameters, the following statement would be used.
CALL get_student_info('John', 'Smith');
Multiple Results using Stored Procedure
By using multiple SELECT
statements, stored procedures can also be used to return multiple results within the stored procedure.
For example, the following stored procedure accepts a department_id
input parameter and returns two result sets: one containing the department information and the other containing the list of employees in that department.
CREATE PROCEDURE get_department_info (department_id int)
BEGIN
SELECT * FROM departments WHERE department_id = department_id;
SELECT * FROM employees WHERE department_id = department_id;
END;
get_department_info
stored procedurePerforming Operations using Stored Procedure
Stored procedures can perform operations that cannot be expressed using a single SQL statement, such as loops and conditional logic. For example, the following stored procedure accepts a start_date
and end_date
input parameter and returns the number of days between the two dates.
CREATE PROCEDURE get_number_of_days (start_date date, end_date date)
BEGIN
DECLARE num_days int;
SET num_days = 0;
WHILE start_date <= end_date DO
SET num_days = num_days + 1;
SET start_date = DATE_ADD(start_date, INTERVAL 1 DAY);
END WHILE;
SELECT num_days;
END;
Parameters
In MySQL, a procedure can have zero or more parameters, which are placeholders for values that are passed when the procedure is called. Parameters enable you to pass input values to the procedure and to receive output values from the procedure.
Here is an example of a simple procedure that has two input parameters and one output parameter:
CREATE PROCEDURE get_employee_name (IN emp_id INT, IN dept_id INT, OUT emp_name VARCHAR(100))
BEGIN
SELECT name INTO emp_name
FROM employees
WHERE id = emp_id AND department_id = dept_id;
END;
In this example, the get_employee_name
procedure takes two input parameters: emp_id
and dept_id
, which are used to identify the employee whose name is to be returned. The emp_name
parameter is an output parameter that will be used to return the employee's name to the caller.
Passing the input parameter
To call this procedure and pass in the input parameters, you can use the following syntax:
CALL get_employee_name(123, 10, @emp_name);
This statement calls the get_employee_name
procedure and passes the values 123
and 10
for the emp_id
and dept_id
parameters, respectively. The @emp_name
variable holds the output value returned from the procedure.
Retrieving the value of the output parameter
After the procedure is called, you can use the SELECT
statement to retrieve the value of the output parameter using SELECT @emp_name;
. This statement will return the employee's name retrieved by the procedure.
MySQL Stored Procedure Disadvantages
There are a few potential disadvantages to using stored procedures in MySQL.
- Non-portable: Stored procedures are typically written in a proprietary language, such as MySQL's Structured Query Language (SQL), which means they are not portable across different database systems. This can make it difficult to migrate a database to a different platform if it relies heavily on stored procedures.
- High memory usage: Using stored procedures can also lead to increased memory and CPU usage due to the added complexity of the logic contained within them.
- Difficult to debug: Stored procedures can be difficult to debug and maintain since they are typically more challenging to read and understand than regular SQL statements.
- Vulnerable to attacks: Stored procedures can be vulnerable to SQL injection attacks if not properly secured, compromising the security of a database.
Lastly,
stored procedures can improve a database's performance by reducing the time and resources required to execute frequently-used SQL queries. Additionally, they can provide an additional layer of security by allowing database administrators to manage which users have access to which stored procedures. Overall, stored procedures can improve the efficiency and security of a MySQL database.
Thank you so much for reading this article, hope you found it helpful. Feel free to give feedbacks and subscribe.