Stored Procedures In MySQL
Prasad Deshmukh
Machine Learning Expert| Optimizing Models with GenAI for Next-Level Engineering | Machine Learning Trainer
When you use MySQL Workbench or mysql shell to issue the query to MySQL Server, MySQL processes the query and returns the result set. If you want to save this query on the database server for execution later, one way to do it is to use a stored procedure.
The following?SELECT?statement returns all rows in the table?customers?from the?sample database:
SELECT?
??customerName,?
??city,?
??state,?
??postalCode,?
??country
FROM
??customers
ORDER BY customerName;
The following?CREATE PROCEDURE?statement creates a new stored procedure that wraps the query above:
DELIMITER $$
CREATE PROCEDURE GetCustomers()
BEGIN
SELECT?
customerName,?
city,?
state,?
postalCode,?
country
FROM
customers
ORDER BY customerName;???
END$$
DELIMITER ;
Once you save the stored procedure, you can invoke it by using the?CALL?statement:
CALL GetCustomers();
The first time you invoke a stored procedure, MySQL looks up for the name in the database catalog, compiles the stored procedure’s code, place it in a memory area known as a cache, and execute the stored procedure.
If you invoke the same stored procedure in the same session again, MySQL just executes the stored procedure from the cache without having to recompile it.
A stored procedure may contain control flow statements such as?IF,?CASE, and?LOOP?that allow you to implement the code in the procedural way.
MySQL stored procedures advantages:
1. Reduce network traffic
2. Centralize business logic in the database
3. Make database more secure
MySQL stored procedures disadvantages:
1. Resource usages
2. Troubleshooting
3. Maintenances