How to use stored procedures in MySQL

Introduction

Typically, when working with a relational database, you issue Structured Query Language (SQL) statements to retrieve or manipulate data, such as SELECT, INSERT, UPDATE, or DELETE, directly from within your application code. These statements operate directly on and manipulate the underlying database tables. If the same statements or group of statements are used in multiple applications that access the same database, they are often repeated across individual applications.

MySQL, like many other relational database management systems, supports the use of stored procedures. Stored procedures help group one or more SQL statements for reuse under a common name, and encapsulate common business logic within the database itself. Such a procedure can be called from an application that accesses the database to retrieve or manipulate data in a consistent manner.

Using stored procedures, you can create reusable routines for common tasks to be used across multiple applications, validate data, or provide an additional layer of data access security by restricting database users from directly accessing underlying tables and issuing arbitrary queries.

In this tutorial, you will learn what stored procedures are and how to create basic stored procedures that return data and use input and output parameters.

Prerequisites

To follow this guide, you need a computer running a SQL-based relational database management system (RDBMS). The instructions and examples in this guide have been validated using the following environment:

• A server running Ubuntu 20.04, with a non-root user with administrative privileges and a firewall configured with UFW
• MySQL is installed and secured on the server.
• Basic introduction to executing SELECT queries to retrieve data from the database

Note: Please note that many RDBMSs use their own unique implementation of SQL and the syntax for stored procedures is not part of the official SQL standard. Although the commands mentioned in this tutorial may work in other RDBMSs, stored procedures are database-specific and therefore the exact syntax or output may differ if you test them on a system other than MySQL.

You will also need an empty database in which you can create tables using stored procedures. We encourage you to follow the Connecting to MySQL and Setting Up a Sample Database section below for details on connecting to a MySQL server and creating the test database used in the examples in this guide.

Connecting to MySQL and setting up a sample database

In this section, you will connect to the MySQL server and create a sample database so you can use the examples in this guide.

For this guide, you will use a fictional car collection. You will store details about the cars you currently own, including their make, model, year of manufacture, and value.

If your SQL database system runs on a remote server, SSH into your server from your local machine:

ssh sammy@your_server_ip

Then open the MySQL server prompt and replace Sami with your MySQL username:

mysql -u sammy -p

Create a database named procedures:

CREATE DATABASE procedures;

If the database was created successfully, you will receive the following output:

Output Query OK, 1 row affected (0.01 sec)

To select the procedures database, run the following USE statement:

USE procedures;

You will get the following output:

Output Database changed

Once you have selected a database, you can create instance tables within it. The table machines will contain simplified data about the machines in the database. It holds the following columns:

• make: This column represents each car owned by it, expressed using the varchar data type with a maximum of 100 characters.
• Model: This column contains the car model name, expressed using the varchar data type with a maximum of 100 characters.
• year: This column stores the year of manufacture of the car with an int data type to hold numeric values.
• Value: This column stores the value of the car using the decimal data type with a maximum of 10 digits and 2 digits after the decimal point.

Create the sample table with the following command:

CREATE TABLE cars ( make varchar(100), model varchar(100), year int, value decimal(10, 2) );

If the following output is printed, the table has been created:

Output Query OK, 0 rows affected (0.00 sec)

Then, load the Cars table with sample data by executing the following INSERT INTO operation:

INSERT INTO cars VALUES ('Porsche', '911 GT3', 2020, 169700), ('Porsche', 'Cayman GT4', 2018, 118000), ('Porsche', 'Panamera', 2022, 113200), ('Porsche', 'Macan', 2019, 27400), ('Porsche', '718 Boxster', 2017, 48880), ('Ferrari', '488 GTB', 2015, 254750), ('Ferrari', 'F8 Tributo', 2019, 375000), ('Ferrari', 'SF90 Stradale', 2020, 627000), ('Ferrari', '812 Superfast', 2017, 335300), ('Ferrari', 'GTC4Lusso', 2016, 268000);

The INSERT INTO operation adds ten sample sports cars to the table, with five Porsche models and five Ferrari models. The following output shows that all five rows have been added:

Output Query OK, 10 rows affected (0.00 sec) Records: 10 Duplicates: 0 Warnings: 0

With that, you are ready to follow the rest of the guide and start using stored procedures in SQL.

Introduction to Stored Procedures

Stored procedures in MySQL and many other relational database systems are called objects that contain one or more instructions that are executed in a sequence when called by the database. In the most basic example, a stored procedure can store a common expression under a reusable routine, such as retrieving data from a database with frequently used filters. For example, you can create a stored procedure to retrieve customers of an online store who have ordered in the past few months. In the most complex scenarios, stored procedures can represent extensive programs that describe complex business logic for powerful applications.

The set of instructions in a stored procedure can include common SQL statements, such as SELECT or INSERT queries, that return or manipulate data. In addition, stored procedures can use the following:

• Parameters are passed to or returned from the stored procedure.
• Variables declared to process retrieved data directly in the procedure code.
• Conditional statements, which allow sections of stored procedure code to be executed depending on certain conditions, such as IF or CASE statements.
• Loops such as WHILE, LOOP, and REPEAT allow sections of code to be executed multiple times, such as for each row in a retrieved dataset.
• Error handling instructions, such as returning error messages to database users who have access to the procedure.
• Calling other stored procedures in the database.

Note: The extensive syntax supported by MySQL makes it possible to write powerful programs and solve complex problems with stored procedures. This guide only covers the basic use of stored procedures with SQL statements enclosed in the stored procedure body, input and output parameters. Conditional code execution, use of variables, loops, and custom error handling are beyond the scope of this guide. We encourage you to learn more about stored procedures in the official MySQL documentation.

When the procedure is called by its name, the database engine executes it as defined, instruction by instruction.

The database user must have the appropriate permissions to execute the given procedure. This permission requirement provides a layer of security, prohibiting direct access to the database while still allowing users access to individual procedures that are safe to execute.

Stored procedures run directly on the database server, performing all calculations locally and returning results to the calling user only upon completion.

If you want to change the behavior of a procedure, you can update the procedure in the database, and applications that use it will automatically receive the new version. All users will immediately start using the new procedure code without having to adjust their applications.

Here is the general structure of the SQL code used to create a stored procedure:

DELIMITER // CREATE PROCEDURE procedure_name(parameter_1, parameter_2, . . ., parameter_n) BEGIN instruction_1; instruction_2; . . . instruction_n; END // DELIMITER ;

The first and last instructions in this code snippet are DELIMITER // and DELIMITER ;. MySQL typically uses the semicolon (;) character to delimit statements and indicate when they begin and end. If you run multiple commands in the MySQL console, separated by semicolons, they are treated as separate commands and run independently, one after the other. However, a stored procedure can contain multiple commands that are executed sequentially when called. This causes a problem when you try to tell MySQL to create a new procedure. The database engine encounters the semicolon character in the body of the stored procedure and thinks it should stop executing the command. In this situation, the command in question is the entire procedure creation code, not a single instruction in the procedure itself, so MySQL misinterprets your intent.

To overcome this limitation, use the DELIMITER statement to temporarily change the delimiter from ; to // for the duration of the CREATE PROCEDURE call. Then, all semicolons within the stored procedure body are sent to the server as is. After the entire procedure is completed, the delimiter is changed to ; with the last DELIMITER ;.

The heart of the code for creating a new procedure is the CREATE PROCEDURE call, followed by the procedure name: procedure_name in the example. The procedure name is followed by an optional list of parameters that the procedure accepts. The last part is the procedure body, enclosed in BEGIN and END statements. Inside the procedure code is what can contain an SQL statement, such as a SELECT query, or more complex code.

The END command ends with //, a temporary separator, instead of a regular semicolon.

In the next section, you create a basic parameterless stored procedure that encapsulates a query.

Creating a stored procedure without parameters

In this section, you create your first stored procedure that contains a SQL SELECT statement to return a list of cars owned by the order by brand and their value in descending order.

Start by executing the SELECT statement you want to use:

SELECT * FROM cars ORDER BY make, value DESC;

The database returns a list of cars from the Cars table, first by brand and then within a brand, by value in descending order:

Output +---------+---------------+------+-----------+ | make model | year | value | +---------+---------------+------+-----------+ | Ferrari | SF90 Stradale 2020 | 627000.00 | | Ferrari | F8 Tributo 2019 | 375000.00 | | Ferrari | 812 Superfast | 2017 | 335300.00 | | Ferrari | GTC4 Lusso 2016 | 268000.00 | | Ferrari | 488 GTB | 2015 | 254750.00 | | Porsche 911 GT3 | 2020 | 169700.00 | | Porsche Cayman GT4 | 2018 | 118000.00 | | Porsche Panamera 2022 | 113200.00 | | Porsche 718 Boxster | 2017 | 48880.00 | | Porsche Macan | 2019 | 27400.00 | +---------+---------------+------+-----------+ 10 rows in set (0.00 sec)

The most valuable Ferrari is at the top of the list, and the least valuable Porsche is at the bottom.

Suppose this query is used repeatedly in multiple applications or by multiple users, and suppose you want to make sure everyone uses the same method for ordering the results. To do this, you want to create a stored procedure that stores that expression under a reusable named procedure.

To create this stored procedure, run the following code snippet:

DELIMITER // CREATE PROCEDURE get_all_cars() BEGIN SELECT * FROM cars ORDER BY make, value DESC; END // DELIMITER ;

As explained in the previous section, the first and last statements (DELIMITER // and DELIMITER 😉) tell MySQL to ignore the semicolon character as a statement separator for the duration of the procedure creation.

The CREATE PROCEDURE SQL statement is followed by the procedure name get_all_cars, which you can define to best describe what the procedure does. After the procedure name, there is a pair of parentheses () where you can add parameters. In this example, the procedure does not use parameters, so the parentheses are empty. Then, between the BEGIN and END statements that define the beginning and end of the procedure code block, the SELECT statement that was used earlier is written verbatim.

The database will respond with a success message:

Output Query OK, 0 rows affected (0.02 sec)

The get_all_cars procedure is now stored in the database and when called, it executes the stored statement as is.

To execute a stored procedure, you can use the CALL SQL command followed by the procedure name. Try executing the newly created procedure like this:

CALL get_all_cars;

The procedure name, get_all_cars, is all you need to use the procedure. You no longer need to manually type any part of the SELECT statement you used before. The database executes the results just like the output of the SELECT statement:

Output +---------+---------------+------+-----------+ | make model | year | value | +---------+---------------+------+-----------+ | Ferrari | SF90 Stradale 2020 | 627000.00 | | Ferrari | F8 Tributo 2019 | 375000.00 | | Ferrari | 812 Superfast | 2017 | 335300.00 | | Ferrari | GTC4 Lusso 2016 | 268000.00 | | Ferrari | 488 GTB | 2015 | 254750.00 | | Porsche 911 GT3 | 2020 | 169700.00 | | Porsche Cayman GT4 | 2018 | 118000.00 | | Porsche Panamera 2022 | 113200.00 | | Porsche 718 Boxster | 2017 | 48880.00 | | Porsche Macan | 2019 | 27400.00 | +---------+---------------+------+-----------+ 10 rows in set (0.00 sec) Query OK, 0 rows affected (0.00 sec)

You have now successfully created a stored procedure without any parameters that returns all the cars from the Cars table ordered in a specific way. You can use the method in multiple applications.

In the next section, you create a procedure that accepts parameters to change the procedure's behavior depending on user input.

Creating a stored procedure with input parameters

In this section, you add input parameters to the stored procedure definition to allow users who run the procedure to pass data to it. For example, users can provide query filters.

The previous stored procedure get_all_cars always retrieves all cars from the cars table. Let's create another procedure to find cars of a given year of manufacture. To allow this, you define a parameter named get_all_cars in the procedure definition.

Run the following code:

DELIMITER // CREATE PROCEDURE get_cars_by_year( IN year_filter int ) BEGIN SELECT * FROM cars WHERE year = year_filter ORDER BY make, value DESC; END // DELIMITER ;

There are several changes to the procedure creation code from the previous section.

First, the name is get_cars_by_year, which describes the method: Retrieve cars based on their year of manufacture.

The previous empty parentheses now contain a parameter definition: IN year_filter int. The IN keyword tells the database that the parameter is being passed to the procedure by the calling user. year_filter is an arbitrary name for the parameter. You will use it to refer to the parameter in the procedure code. Finally, int is the data type. In this case, the year of manufacture is expressed as a numeric value.

The year_filter parameter defined after the procedure name appears in the SELECT statement in the WHERE year = year_filter clause and filters the table of cars by their year of manufacture.

The database will respond once again with a success message:

Output Query OK, 0 rows affected (0.02 sec)

Try running the procedure without passing any parameters to it, just like before:

CALL get_cars_by_year;

The MySQL database returns an error message:

Error message ERROR 1318 (42000): Incorrect number of arguments for PROCEDURE procedures.get_cars_by_year; expected 1, got 0

This time, the stored procedure expects a parameter to be provided, but none was provided. To call a stored procedure with parameters, you can provide the parameter values inside parentheses in the same order that the procedure expects. To retrieve cars manufactured in 2017, run the following:

CALL get_cars_by_year(2017);

Now the called procedure executes correctly and returns the list of cars for that year:

Output +---------+---------------+------+-----------+ | make model | year | value | +---------+---------------+------+-----------+ | Ferrari | 812 Superfast | 2017 | 335300.00 | | Porsche 718 Boxster | 2017 | 48880.00 | +---------+---------------+------+-----------+ 2 rows in set (0.00 sec) Query OK, 0 rows affected (0.00 sec)

In this example, you learned how to pass input parameters to stored procedures and use them in queries within a procedure to provide filter options.

In the next section, you will use output parameters to create procedures that return multiple different values in a single execution.

Creating a stored procedure with input and output parameters

In both of the previous examples, the stored procedures you created called the SELECT statement to return a set of results. But in some cases, you might need a stored procedure that returns several different values together instead of a single result set for a query.

Suppose you want to create a procedure that provides summary information about cars of a given year, including the number of cars in the collection and their market value (minimum, maximum, and average).

To do this, you can use OUT parameters when creating a new stored procedure. Similar to IN parameters, OUT parameters have a name and data type associated with them. However, instead of passing data to the stored procedure, they can be filled with data from the stored procedure to return values to the calling user.

Create a get_car_stats_by_year procedure that returns summary data for cars from a given production year, using output parameters:

DELIMITER // CREATE PROCEDURE get_car_stats_by_year( IN year_filter int, OUT cars_number int, OUT min_value decimal(10, 2), OUT avg_value decimal(10, 2), OUT max_value decimal(10, 2) ) BEGIN SELECT COUNT(*), MIN(value), AVG(value), MAX(value) INTO cars_number, min_value, avg_value, max_value FROM cars WHERE year = year_filter ORDER BY make, value DESC; END // DELIMITER ;

This time, along with the IN parameter year_filter, which is used to filter cars by year of manufacture, there are four OUT parameters defined in the parentheses block. The cars_number parameter is represented by the int data type and is used to return the number of cars in the set. The min_value, avg_value, and max_value parameters represent the market value and are defined with the decimal type (10, 2) (similar to the value column in the cars table). These are used to return information about the cheapest and most expensive cars in the set, as well as the average price of all matching cars.

The SELECT statement looks up four values ​​from the cars table using SQL mathematical functions: COUNT to get the total number of cars and MIN, AVG, and MAX to get the minimum, average, and maximum values ​​from the value column.

To tell the database that the results of that query should be stored in the output parameters of the stored procedure, a new keyword called INTO has been introduced. After the INTO keyword, the names of the four procedure parameters related to the retrieved data are listed. This will cause MySQL to store the value of COUNT(*) in the cars_number parameter, the result of MIN(value) in the min_value parameter, and so on.

The database confirms the successful creation of the procedure:

Output Query OK, 0 rows affected (0.02 sec)

Now run the new routine by running the following:

CALL get_car_stats_by_year(2017, @number, @min, @avg, @max);

The four new parameters start with the @ symbol. These are the names of local variables in the MySQL console that you can use to temporarily store data. When you pass them to the stored procedure you created, the procedure inserts values into those variables.

The database will respond with the following:

Output Query OK, 1 row affected (0.00 sec)

This is different from the previous behavior, where the results were immediately displayed on the screen. This is because the results of the stored procedure are stored in output parameters and are not returned as query results. To access the results, you can select them directly in the MySQL shell as follows:

SELECT @number, @min, @avg, @max;

With this query, you select values from local variables, rather than calling the procedure again. The stored procedure stores its results in those variables, and the data is available until you exit the shell.

Note: For more information about using user-defined variables in MySQL, see the User-Defined Variables section of the documentation. When used in application development, the ways to access data returned from stored procedures will vary across different programming languages and frameworks. If in doubt, refer to the documentation for your chosen language and framework.

The output displays the values of the query variables:

Output +---------+----------+-----------+-----------+ | @number | @min | @avg | @max | +---------+----------+-----------+-----------+ | 2 48880.00 | 192090.00 | 335300.00 | +---------+----------+-----------+-----------+ 1 row in set (0.00 sec)

The values ​​correspond to the number of cars produced in 2017, as well as the minimum, average, and maximum market value of cars from this production year.

In this example, you learned how to use output parameters to return multiple different values from within a stored procedure for later use. In the next section, you will learn how to delete created procedures.

Delete stored procedures

In this section, you will delete the stored procedures in the database.

Sometimes a procedure you created may no longer be needed. In other situations, you may want to change the way the procedure works. MySQL does not allow you to change the definition of a procedure after it has been created, so the only way is to first drop the procedure and then recreate it with the desired changes.

Let's drop the last procedure, get_car_stats_by_year. To do this, you can use the DROP PROCEDURE statement:

DROP PROCEDURE get_car_stats_by_year;

The database confirms the successful deletion of the procedure with a success message:

Output Query OK, 0 rows affected (0.02 sec)

You can verify that the procedure has been deleted by attempting to call it. Run:

CALL get_car_stats_by_year(2017, @number, @min, @avg, @max);

This time, you will see an error message that the procedure does not exist in the database:

Error message ERROR 1305 (42000): PROCEDURE procedures.get_car_stats_by_year does not exist

In this section, you learned how to delete stored procedures from a database.

Result

By following this guide, you learned what stored procedures are and how to use them in MySQL to store reusable statements in named procedures and execute them later. You created stored procedures without parameters and procedures that use input and output parameters to make them more flexible.