An Introduction To Stored Procedures In MySQL

Many relational database management systems today can use stored procedures to execute tasks on the database.

Stored procedures can be extremely useful in improving the efficiency of your database, saving you time, money, and lots of work-related headaches.

So let’s dive into exactly how they can help you.

What is a stored procedure?

A stored procedure is a precompiled set of one or more SQL statements stored in a relational database management system that can be reused and shared by multiple programs for the purpose of executing tasks on the database. 

A stored procedure is usually assigned a name, e.g. get_all_films, and it can accept input and output parameters. They share some similarities with functions in procedural programming languages.

Advantages of stored procedures

Stored procedures have some great benefits. When used properly, they help you build powerful database applications and can also offer better performance, higher productivity, ease of use and increased scalability. 

Performance

By grouping one or more SQL statements, you can execute the statements in a single call, eliminating network bottlenecks and roundtrips. 

Also, by moving procedures from the client to the server you are able to free the client’s resources and take advantage of the server’s computing resources where they will execute even faster. Additionally, stored procedures are compiled once, cached and stored in an executable form which makes them very quick and efficient whenever they are called, and as a result, lowers memory requirements.

Productivity and ease of use

Stored procedures boost productivity by helping you avoid redundant coding – you can group related tasks in a single place and call them whenever you want. They also extend the functionality of the RDBMS (Relational Database Management System), for example, by calling functions from your SQL statements. 

Furthermore, stored procedures can also be shared between multiple programs, letting you share business logic across applications.

Security

Stored procedures are secure. You can allow users to manipulate data through a stored procedure with predefined privileges. 

For example, you can allow users to access a stored procedure that updates a table but deny access to the table itself.

Managing stored procedures in MySQL

Creating a stored procedure

To create a procedure, you can either make use of the MySQL command-line client or MySQL workbench. When creating procedures you wrap a series of SQL queries in the CREATE PROCEDURE statement. The syntax is as follows:

DELIMITER //

CREATE PROCEDURE procedure_name([procedure_parameter[,...]])
BEGIN
statements;
END //

DELIMITER ;Code language: SQL (Structured Query Language) (sql)

When writing stored procedures, the general rule of thumb is to:

1. Assign a name for the procedure (procedure_name)
2. Specify a comma-separated list of parameters for the procedure, if any.
3. Write your statements that will be executed by the procedure between the BEGIN and END block.

Because the MySQL statements are usually terminated with the semi-colon ;, we need to introduce a different delimiter // using the DELIMITER statement. This will allow you to write the queries you want to execute in a familiar syntax.

Calling/executing a stored procedure

The syntax for executing  a stored procedure is simple. You use the CALL statement followed by the name of the stored procedure you want to execute:

CALL procedure_name()Code language: SQL (Structured Query Language) (sql)

Listing all created stored procedures

You can list all the procedures you have created for a particular database by running the following command:

SHOW PROCEDURE STATUS WHERE db = database_name;Code language: SQL (Structured Query Language) (sql)

Deleting a stored procedure

To delete a stored procedure, you can call the DROP PROCEDURE statement followed by the name of the stored procedure you wish to delete:

DROP PROCEDURE procedure_name;Code language: SQL (Structured Query Language) (sql)

Altering/modifying an existing stored procedure

Currently, there is no statement available to change the parameters or SQL statements in the body of a stored procedure. Because of this reason, we can not alter the content of a stored procedure via the MySQL command-line (except for comments). 

However, you can do that via the MySQL Workbench GUI using the following steps:

1. In the Navigator pane, right click the name of the stored procedure you want to modify, and select or click Alter Stored Procedure…

2. In the window that opens, make your changes and click Apply:

Putting it together

Let us create a stored procedure using what we learned above. Our first stored procedure example is fairly simple, it fetches the list of films in our database when called.

Before we can create a procedure though, we need to tell MySQL to use the database we would like to create the procedure in. In this guide, I will be using the famous sakila database, available in the MySQL documentation website.

USE sakila;Code language: SQL (Structured Query Language) (sql)

The following lines will create a stored procedure named get_all_films in the sakila database:

DELIMITER //

CREATE PROCEDURE get_all_films()BEGIN
SELECT * FROM film;
END //

DELIMITER ;Code language: SQL (Structured Query Language) (sql)

The following line will then execute the stored procedure and return all the films in the database:

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

By defining our film fetching logic in a stored procedure, we can execute the same logic or tasks repeatedly by simply calling get_all_films(). It gets even better when we need to do more complex tasks in a single call as you will see in the next example.

Using parameters in stored procedures

MySQL stored procedures support three types of parameters:

1.  The IN parameter

This parameter works just like function parameters found in many programming languages like JavaScript, Python, etc. IN parameters are the default method to pass values that will be used by the statements in a stored procedure. The value of an IN parameter can be passed as values in the statements you write in a stored procedure.

The value of an IN parameter is always protected, this means that when you modify the value of the parameter anywhere in the body of the stored procedure, the original value remains unchanged.

2. The OUT parameter

The OUT parameter is used by the calling program, most times the result of the statements you run in the stored procedure can be stored as a value in the OUT parameter and can then be retrieved later. The OUT parameter’s original value can also be modified inside the stored procedure.

3. The INOUT parameter

The INOUT parameter combines the functionality of the IN and OUT parameter types. This means that it can be used for passing arguments into the stored procedure and its value can also be retrieved by the calling program.

The stored procedure we ran above lacked parameters, so let us create another one that has them so that we can add a bit more enhanced functionality. Let’s say we want to find all films that are in stock in our database; we can add some parameters that can be used in dynamically filtering the data for this particular attribute. This is what the stored procedure would look like:

DELIMITER //

CREATE PROCEDURE `film_in_stock`(IN p_film_id INT, IN p_store_id INT, OUT p_film_count INT)
BEGIN
    SELECT inventory_id
    FROM inventory
    WHERE film_id = p_film_id
    AND store_id = p_store_id
    AND inventory_in_stock(inventory_id);

    SELECT COUNT(*)
    FROM inventory
    WHERE film_id = p_film_id
    AND store_id = p_store_id
    AND inventory_in_stock(inventory_id)
    INTO p_film_count;
END //

DELIMITER ;Code language: SQL (Structured Query Language) (sql)

In the example above, we introduced three parameters using two of the parameter types – IN and OUT:

1. p_film_id defines the variable for the film’s id
2. p_store_id defines the variable for the store’s id
3. p_film_count will hold the total number of films that were found in the inventory using the filters passed.

We would call the procedure by passing the required arguments as follows:

mysql> CALL film_in_stock(1, 1, @p_film_count);
+--------------+
| inventory_id |
+--------------+
|            1 |
|            2 |
|            3 |
|            4 |
+--------------+
4 rows in set (0.01 sec)Code language: SQL (Structured Query Language) (sql)

When we called the procedure, we asked MySQL to run our stored procedure to find all the films in the inventory table, where the film id is 1 and the store id is 1. We also passed a third argument, which is a variable name that will hold the total count value of the results.

We can retrieve the results by running a SELECT statement followed by the variable name as follows:

mysql> SELECT @p_film_count;
+---------------+
| @p_film_count |
+---------------+
|             4 |
+---------------+
1 row in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)

Note that the variable name we passed as our third argument doesn’t have to be the exact spelling as the parameter we defined when creating the stored procedure. We could as well just use @p_count if we wanted to.

Conclusion

From the points and examples above, we can see how very powerful and useful stored procedures can be when used in MySQL. I use them often to avoid repeating tasks that I would love to execute more than once. 

Apart from saving you time, they also help you move some logic you would normally write in your application to the database, thus enabling you to focus more on the business logic of your application. 

In our next tutorial, we’d visit some more advanced concepts of using stored procedures in MySQL. Till then, feel free to check out some of our other resources covering performance tuning in MySQL in the links below.

How to Use Indexes to Increase MySQL Database Performance

How to Optimize MySQL Database Schema for Improved Performance

How to Optimize Query Performance in MySQL Databases

I’m Elvis Duru. I create helpful content for web developers with a focus on React, Node.js, SQL/NoSQL Databases, and more! Let’s connect on Twitter.

References

Advantages of Stored Procedures – Oracle8i Java Stored Procedures Developer’s Guide