MySQL Stored Procedures
MySQL provides us with the ability to create stored procedures. Stored procedures are a powerful part of MySQL (and other database management systems, such as SQL Server) and they allow you to do more than views do.
A stored procedure is a collection of SQL statements that are stored in the database. A stored procedure can contain business logic, which is one of the key aspects that distinguishes stored procedures from views. A stored procedure can accept parameters, and you can set variables, write
IF statements, etc within a stored procedure.
How Do Stored Procedures Work?
First of all, you create the stored procedure. Then once it has been created, you can run it (or more precisely, you "call" it).
To run a stored procedure you "call" it. When you call it, you also provide any parameters that it might require. The stored procedure will then execute, using your parameters in any way that the code specifies.
For example, you could write a stored procedure that accepts a
FruitId parameter. The stored procedure could then take that parameter and use it to check the inventory for that particular fruit. Therefore, you could call the stored procedure, each time with a different fruit ID and it would return a value that shows you how much of that fruit is in stock.
Create a Stored Procedure
Stored procedures are created using the
CREATE PROCEDURE statement.
Here's the syntax for creating a stored procedure:
Replace sp_name with whatever name you'd like to use for the stored procedure. The parentheses are required — they enclose any parameters. If no parameters are required, the parentheses can be empty.
The main body of the stored procedure goes in between the
END keywords. These keywords are used for writing compound statements. A compound statement can contain multiple statements, and these can be nested if required. Therefore, you can nest
In most cases, you will also need to surround the
CREATE PROCEDURE statement with
DELIMITER commands and change
END //. Like this:
I'll explain why soon, but for now, let's look at an example.
Here's a simple example of creating a stored procedure. Running the following code against our FruitShop database will create a stored procedure called spCheckFruitStock:
Now we can call that stored procedure like this:
Here, we pass a parameter of
1 which is the ID for Apple.
Here's the result:
We can do the same for any fruit in our database, simply by changing the parameter passed to the stored procedure.
In the above example, we added a couple of
DELIMITER commands and we replaced a semicolon with two forward slashes. What's going on here?
We did this to tell MySQL to use a different delimiter while it creates our stored procedure.
The reason for this is that, MySQL already recognizes the semicolon as a delimiter for marking the end of each SQL statement. Therefore, as soon as MySQL sees the first semicolon, it will interpret the delimiter as such and our stored procedure would break.
DELIMITER command allows us to tell MySQL to use a different delimiter. In the above example we set this to two forward slashes (
//) but this could've been anything (although, avoid using a backslash (
\) as that is the escape character for MySQL). By changing the delimiter, MySQL won't try to interpret our semicolons as the end of the statement — it will wait until it sees the two forward slashes.
Once we've created the stored procedure, we can use
DELIMITER ; to reset the delimiter back to the semicolon.
Dropping a Stored Procedure
You can drop a stored procedure by using the
DROP PROCEDURE statement. Like this:
Altering a Stored Procedure
You can alter some aspects of a stored procedure by using the
ALTER PROCEDURE statement.
However, to change the body of the stored procedure, or any of its parameters, you need to drop the procedure and create it again. Like this:
Here, we added
Fruit.FruitId to the list of columns to return.
A More Advanced Stored Procedure
The above example was a simple one in order to demonstrate the syntax of creating and calling stored procedures. Let's look at a slightly more complex stored procedure:
The above example accepts two different modes of parameters (
IN is the default, so this is why the previous example didn't include the mode.
Here, we also set a variable. We use
DECLARE stockNumber SMALLINT to declare a variable called
stockNumber with a type of
SMALLINT (small integer).
We use a
SELECT statement to look up the inventory for the given fruit ID and assign that into our
Finally, we use a SQL
IF statement to determine the stock level, placing this value into the
pStockLevel parameter (which of course is the
OUT parameter — this is the value that we will see when we call the stored procedure).
Calling a Stored Procedure with an
In our last example we specified two parameters, an
IN parameter and an
When we call this stored procedure, we still need to include the
OUT parameter. However, because we won't know its value (after all, that is why we're calling it — to find out its value!), we will need to use a variable. Then we can use a
SELECT statement to find out its value.
We just used two parameter modes (
OUT). In MySQL, there are three parameter modes that can be used with stored procedures.
When you use this parameter mode, you (or your application) must pass the parameter's value when you call the stored procedure. These parameters are protected. Therefore, its original value is retained after the stored procedure has been executed. If the stored procedure changes the value, it only does so on a copy of the parameter.
This mode is the default mode. If you don't provide the parameter mode, it will be
- The value of an
OUTparameter can change within the stored procedure, and its value is returned to the calling application.
- This mode is a combination of the
OUTmodes. You can pass the initial value, the stored procedure can change it, and it will return the new value to the calling application.