In part one of this three part series, we went over the steps necessary to create a stored function in MySQL. In part two of this series we are going to introduce you to stored procedures.
A stored procedure is different from a stored function in that a stored procedure has no return value but has the ability to manipulate tables and data. Continuing with our scenario, we have a table that contains a list of films.
The data we want to work with is the rental rate for the films in our video rental store. Due to the fact all of these films were released in 2006, we need to reduce the rental rate of these films. We will use a stored procedure to accomplish this task.
Stored routines require MySQL 5.0.1 or higher. Make sure you are using at least MySQL 5.0.1. Once again, we are going to work with the example database from MySQL called Sakila. It can be downloaded from MySQL at http://downloads.mysql.com/docs/.
At the time of this post, the file is called sakila-db and it’s available as a zip or tarball. Specifically, we are going to work with the film table that is in the Sakila database.
In this post we will also be working with phpMyAdmin. This program is very common and wide spread. If you have a web host, you probably have access to phpMyAdmin to work with your databases. If you have command line access to your MySQL databases, the examples should work just as well.
Later in this post, when you actually use the CALL command to run your stored procedure, if you receive an “out of range” error when you CALL the stored procedure, your MySQL installation is probably setup with a strict SQL mode. On windows, if you view your my.ini file, it probably looks like this:
It needs to look like this:
If you don’t want to modify you’re my.ini file, you can type the following into your MySQL command line or into phpMyAdmin:
SET SESSION sql_mode=”NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION”;
In the command above, we are only changing the session mode. That means the change only exists for the length of your current session. If you use the following command you will change the mode permanently:
SET GLOBAL sql_mode=”NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION”;
Let’s get started, shall we?
Creating A Stored Procedure
The first step in creating a stored procedure is informing MySQL of the delimiting character you are going to use. For our example, we will be using // for delimiting statements and commands.
This prevents MySQL from ending the stored procedure as soon as it encounters a ; character. In phpMyAdmin you declare your delimiter in the Delimiter box just below the SQL window. Once we declare our delimiter, our SQL window looks like this:
Our next step is the CREATE PROCEDURE command. We will call our new stored procedure update_rental_rate_price. The CREATE PROCEDURE command must always have () after the procedure’s name regardless if you are passing in parameters or not.
For our example, we want to pass a parameter into the stored procedure. This parameter will be a percentage that is used in the new rental rate calculation for each film. Our SQL window now looks like this:
Once again, if you wanted to use more than one parameter, you would separate them with a comma.
Since stored procedures never have a return value, we don’t have to worry about creating a return variable type. If your stored procedure only has one command, you don’t have to use the BEGIN or END statements.
In our scenario, we are going to perform two commands so our next step is informing MySQL we want to begin the body of our stored procedure. Again, we use the BEGIN command on a line by itself. Our SQL window now looks like this:
For the body of our new stored procedure, we want to perform a calculation that decreases the rental rates of films and then we want to see the results. Notice we are indenting two spaces to make the body easier to see. This is not a requirement, but it is useful. Our two queries will consist of an UPDATE and then a SELECT CONCAT. Our SQL window now looks like this:
The final step in creating our stored procedure is actually using our new stored procedure. Stored procedures are invoked using the CALL command, the name of the stored procedure and a set of (). Remember to place any parameters within the (). We want to reduce the price of our rentals by 15% in our example. Invoking our new stored procedure looks like this:
And the result looks like this:
And to show you the change actually occurred for each record, here’s a before and after using the following query:
SELECT `title`, `description`, `rental_rate` from film;
In this post we covered another member of the stored routine family. We covered how to create a stored procedure. Remember, stored procedures never return a value and you must use () after the stored procedure’s name regardless if you are passing in parameters or not.
In part three of this series we will cover a larger scope for stored routines. We will also offer a couple of the “tricks of the trade” regarding stored routines. Stay tuned.