Introduction to MySQL Stored Procedures
Summary: in this tutorial, you will learn about MySQL stored procedures, their advantages, and disadvantages.
Getting started with stored procedures
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;Code language: SQL (Structured Query Language) (sql)
This picture shows the partial output of the query:
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 CREATE PROCEDURE statement creates a new stored procedure that wraps the query above:
DELIMITER $$BEGIN
SELECT
customerName,
city,
state,
postalCode,
country
FROM
customers
ORDER BY customerName;
END$$
DELIMITER ;
Code language: SQL (Structured Query Language) (sql)
By definition, a stored procedure is a segment of declarative SQL statements stored inside the MySQL Server. In this example, we have just created a stored procedure with the name GetCustomers().
Once you save the stored procedure, you can invoke it by using the CALL statement:
CALL GetCustomers();Code language: SQL (Structured Query Language) (sql)
And the statement returns the same result as the query.
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 can have parameters so you can pass values to it and get the result back. For example, you can have a stored procedure that returns customers by country and city. In this case, the country and city are parameters of the stored procedure.
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.
A stored procedure can call other stored procedures or stored functions, which allows you to modulize your code.
Note that you will learn step by step how to create a new stored procedure in the next tutorial.
MySQL stored procedures advantages
The following are the advantages of stored procedures.
Reduce network traffic
Stored procedures help reduce the network traffic between applications and MySQL Server. Because instead of sending multiple lengthy SQL statements, applications have to send only the name and parameters of stored procedures.
Centralize business logic in the database
You can use the stored procedures to implement business logic that is reusable by multiple applications. The stored procedures help reduce the efforts of duplicating the same logic in many applications and make your database more consistent.
Make database more secure
The database administrator can grant appropriate privileges to applications that only access specific stored procedures without giving any privileges on the underlying tables.
MySQL stored procedures disadvantages
Besides those advantages, stored procedures also have disadvantages:
Resource usages
If you use many stored procedures, the memory usage of every connection will increase substantially.
Besides, overusing a large number of logical operations in the stored procedures will increase the CPU usage because the MySQL is not well-designed for logical operations.
Troubleshooting
It’s difficult to debug stored procedures. Unfortunately, MySQL does not provide any facilities to debug stored procedures like other enterprise database products such as Oracle and SQL Server.
Maintenances
Developing and maintaining stored procedures often requires a specialized skill set that not all application developers possess. This may lead to problems in both application development and maintenance.
In this tutorial, you have learned about the MySQL stored procedures, their advantages, and disadvantages. Let’s move on to learn how to create a new stored procedure in MySQL.