Table of Contents
- 1| What is MySQL Stored Procedure
- 2| Creating Stored Procedure
- 3| Variable declarations inside Stored Procedure
- 4| Using IF statements inside Stored Procedure
- 5| Iterate over SELECT result in a LOOP inside Stored Procedure
- 6| Exiting a Stored Procedure and Throwing Error Message
Stored Procedures in MySQL are the typical primer for great ideas and bad implementation. Even that I have stored procedures, because of their performance penalties, recently I had to write one. The procedure itself was very simple, but there were some catchy stages I would like to describe in case I need to write such again.
1| What is MySQL Stored Procedure
This is the mysql thing for “programming inside mysql” and yes you could write your own functions which are mostly limited to using SQL-derived statements. The functions could be given parameters, could return parameters, exit codes and so on.
If you need “more” on what are Stored Procedures and why do they exists, I’m not the right guy to tell you, so get your ticket to dev.mysql.com or something more eligible resource
2| Creating Stored Procedure
The first step you will encounter is to create the procedure.
Comments in SQL and Stored Procedures could be one of these:
/* ….some commented multilined text …..*/
— This is one line comment
My test procedure (named new_schedule) looked like this:
( I have added comments describing each row)
-- Drop the procedure if it already exists DROP PROCEDURE IF EXISTS new_schedule; -- Change the line-end delimiter DELIMITER $$ -- This is the actual create procedure statement -- Our procedure accepts 2 arguments -- IN means we are able only to read the arguments -- Every argument has also type defined (char(3) or date) CREATE DEFINER=`root`@`localhost` PROCEDURE `new_schedule`(IN courseCode char(3), IN startDate date) BEGIN SELECT * FROM some_table; -- End of procedure definition END$$ DELIMITER ;
From now on, every code example I give is supposed to be inside stored procedure definition, so I won’t add again and again the procedure definitions
3| Variable declarations inside Stored Procedure
As with most of the programming languages, here you could also declare variables. Stored procedures in mysql have “strict type” declarations, which means that you must also mention the type of the variable you are declaring.
[Important Note] It is very important to make sure your variable declarations are at the beginning of the stored procedure. If you try to declare variable later on in the procedure, mysql will throw you some nasty and hard to debug errors.
[/ Important Note]
Some example declarations:
DECLARE integer_var INTEGER DEFAULT 0; DECLARE my_string varchar(100) DEFAULT ""; DECLARE date_var date DEFAULT NULL; DECLARE varchar_var varchar(30) DEFAULT ""; -- Assign value to an already defined variable SET my_string = "Set the variable to some text"
Whatever variable you plan to use inside the procedure, must be firstly defined in the beginning. Later you could assign it a value.
If you have any cursor or handler declarations ( which are used for looping), you must always use the following declarations order:
- First must be variable/conditional delcarations
- Second are cursor declarations
- Third are handler declarations
[/ Important Note]
4| Using IF statements inside Stored Procedure
The next “cool” thing is you have “Flow control statements” or more precisely:
CASE , IF/THEN/ELSE and even WHILE and LOOP statements.
I will give example for using the IF/THEN/ELSE and also iterating through a LOOP
DECLARE operator VARCHAR(20); DECLARE m int; SET m=5; IF m > 3 THEN SET s="bigger"; ELSEIF m < 3 THEN SET s="smaller"; ELSE SET s="equal"; END IF;
5| Iterate over SELECT result in a LOOP inside Stored Procedure
One of the thing you will almost 100% need is doing a LOOP. Usually this is when you want to process all result rows from a given select.
Here is one way to use the internal LOOP keyword inside stored procecdure;
First we will declare CURSOR variable which will give us the chance to iterate over result set by using the keyword FETCH . FETCH returns one value at a time.
We will also use HANDLER declaration which is used to determine if we have empty result set returned form the SELECT and so don’t iterate into the loop.
Here is the example:
-- Initialize loop_done variable to 0 DECLARE loop_done INTEGER DEFAULT 0; -- Temporary variable for iterating days in it DECLARE temp_day INTEGER ; -- Declare CURSOR -- The following SELECT will select for us 7 rows (each containing a DAY) from the day table DEClARE select_cursor CURSOR FOR SELECT date FROM days WHERE week='3'; -- Define CONTINUE HANDLER -- Whenever our result set END after doing the LAST FETCH, our -- continue handler will set loop_done to 1 in order to leave the LOOP DECLARE CONTINUE HANDLER FOR NOT FOUND SET loop_done = 1; -- Open the CURSOR open select_cursor; -- Start the LOOP select_loop: LOOP FETCH select_cursor INTO temp_day; IF loop_done = 1 THEN -- LEAVE is a keyword used to END the loop LEAVE select_loop; END IF; -- Print the day SELECT "We are processing day:", temp_day; END LOOP select_loop; -- Don't forget to close the CURSOR CLOSE select_cursor;
6| Exiting a Stored Procedure and Throwing Error Message
Sometimes you may need to exit a procedure in such a way that it will return usable message to the CALLER. The caller could be you if you manually execute the procedure by using CALL() or another stored procedure.
You can exit the procedure and return message by using the “SIGNAL” keyword.
More information on SIGNALs could be found here:
Here is a simple example I was using in my procedure to return an error:
IF some_int < 10 THEN SIGNAL SQLSTATE '45001' SET MESSAGE_TEXT = 'Some_int is too small and is smaller than 10'; END IF;