Introduction to MySQL Stored Procedures

Summary: in this tutorial, you will learn about MySQL stored procedures, including 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 the mysql shell to execute a query on the MySQL Server, the server processes the query and returns the result set.

If you intend to save this query on the database server for later execution, one way to achieve this is by using a stored procedure.

The following CREATE PROCEDURE statement creates a new stored procedure encapsulating the query above:

DELIMITER $$

CREATE PROCEDURE GetCustomers()
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 set of declarative SQL statements stored within the MySQL Server. In this example, we have just created a stored procedure named GetCustomers().

After saving the stored procedure, you can invoke it by using the CALL statement:

CALL GetCustomers();Code language: SQL (Structured Query Language) (sql)

The statement returns the same result as the query.

The initial invocation of a stored procedure involves the following actions by MySQL:

• First, find the stored procedure by its name in the database catalog.
• Second, compile the code of the stored procedure.
• Third, store the compiled stored procedure in a cache memory area.
• Finally, execute the stored procedure.

If you invoke the same stored procedure again within the same session, MySQL will execute it from the cache without the need for recompilation.

A stored procedure can have parameters, allowing you to pass values to it and retrieve results.

For example, you can define a stored procedure that returns customers by country and city. In this case, the country and city are parameters of the stored procedure. Additionally, a stored procedure may incorporate control flow statements such as IF, CASE, and LOOP.

A stored procedure can call other stored procedures or stored functions, enabling you to organize your code more effectively.

Please note that in the upcoming tutorial, you learn step-by-step how to create a new stored procedure.

MySQL Stored Procedures advantages

Below are the advantages of stored procedures:

• Reduce network traffic – Stored procedures help reduce the network traffic between applications and MySQL servers. Instead of sending multiple lengthy SQL statements, applications only need to send the name and parameters of the stored procedures.
• Centralize business logic in the database – You can use stored procedures to implement reusable business logic across multiple applications. They streamline the process, reducing the need to duplicate the same logic in multiple applications and contributing a more consistent database.
• Make the database more secure – You can grant specific privileges to applications, allowing access to particular stored procedures without providing any privileges to the underlying tables.

MySQL stored procedures disadvantages

In addition to these advantages, stored procedures also have disadvantages:

• Resource usage – If you use many stored procedures, the memory usage of every connection will significantly increase. Additionally, an excessive use of logical operations in the stored procedures can lead to increased CPU usage, as MySQL is not well-designed for such operations.
• Troubleshooting – Debugging stored procedures is quite challenging. Unfortunately, MySQL lacks facilities for debugging stored procedures, a feature available in other enterprise database products such as Oracle Database and SQL Server.
• Maintenances – Developing and maintaining stored procedures often demands a specialized skill set not universally possessed by all application developers, potentially causing issues in both application development and maintenance.

Summary

• A stored procedure is a wrapper of a set of SQL statements stored in the MySQL database server.
• The advantages of stored procedures include reduced network traffic, enhanced code reusability, improved security through controlled access, streamlined implementation of business logic, and the ability to grant specific privileges to applications without exposing underlying database structures.
• The disadvantages of stored procedures include increased memory usage for each connection, challenges in debugging due to a lack of dedicated tools, and the necessity for a specialized skill set, which not all application developers may possess, leading to potential difficulties in both development and maintenance processes.