Stored Procedures

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;

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 $$

CREATE PROCEDURE GetCustomers()
BEGIN
	SELECT 
		customerName, 
		city, 
		state, 
		postalCode, 
		country
	FROM
		customers
	ORDER BY customerName;    
END$$
DELIMITER ;

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();

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.