MySQL Stored Procedures
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.
Syntax
Here's the syntax for creating a stored procedure:
CREATE PROCEDURE sp_name(p_1 INT)
BEGIN
...code goes here...
END;
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 BEGIN and 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 BEGIN and END blocks.
In most cases, you will also need to surround the CREATE PROCEDURE statement with DELIMITER commands and change END; to END //. Like this:
DELIMITER //
CREATE PROCEDURE sp_name(p_1 INT)
BEGIN
...code goes here...
END //
DELIMITER ;
I'll explain why soon, but for now, let's look at an example.
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:
DELIMITER //
CREATE PROCEDURE spCheckFruitStock(thisFruit SMALLINT)
BEGIN
SELECT
Fruit.FruitName,
Fruit.Inventory,
Units.UnitName
FROM
Fruit INNER JOIN Units ON
Fruit.UnitId = Units.UnitId
WHERE
Fruit.FruitId = thisFruit;
END //
DELIMITER ;
Now we can call that stored procedure like this:
CALL spCheckFruitStock(1);
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.
About the DELIMITER Command
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.
The 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:
DROP PROCEDURE spCheckFruitStock;
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:
DROP PROCEDURE IF EXISTS spCheckFruitStock;
DELIMITER //
CREATE PROCEDURE spCheckFruitStock(thisFruit SMALLINT)
BEGIN
SELECT
Fruit.FruitId,
Fruit.FruitName,
Fruit.Inventory,
Units.UnitName
FROM
Fruit INNER JOIN Units ON
Fruit.UnitId = Units.UnitId
WHERE
Fruit.FruitId = thisFruit;
END //
DELIMITER ;
Here, we added Fruit.FruitId to the list of columns to return.
Result:
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:
DROP PROCEDURE IF EXISTS spCheckFruitStockLevel;
DELIMITER //
CREATE PROCEDURE spCheckFruitStockLevel(
IN pFruitId SMALLINT(5),
OUT pStockLevel VARCHAR(6))
BEGIN
DECLARE stockNumber SMALLINT;
SELECT
Fruit.Inventory into stockNumber
FROM
Fruit INNER JOIN Units ON
Fruit.UnitId = Units.UnitId
WHERE
Fruit.FruitId = pFruitId;
IF stockNumber > 10 THEN
SET pStockLevel = 'High';
ELSEIF (stockNumber <= 10 AND stockNumber >= 5) THEN
SET pStockLevel = 'Medium';
ELSEIF (stockNumber < 5) THEN
SET pStockLevel = 'Low - Please Replace Now!';
END IF;
END //
DELIMITER ;
The above example accepts two different modes of parameters (IN and OUT). 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 stockNumber variable.
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 OUT or INOUT Parameter
In our last example we specified two parameters, an IN parameter and an OUT parameter.
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.
Like this:
CALL spCheckFruitStockLevel(1, @stockLevel);
select @stockLevel;
Result:
Parameter Modes
We just used two parameter modes (IN and OUT). In MySQL, there are three parameter modes that can be used with stored procedures.
IN
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 IN.
OUT
The value of an OUT parameter can change within the stored procedure, and its value is returned to the calling application.
INOUT
This mode is a combination of the IN and OUT modes. You can pass the initial value, the stored procedure can change it, and it will return the new value to the calling application.
