MySQL Stored Procedures: Part 2

Characteristics

Last month we created skeleton stored procedures, to introduce the concepts. Let's start off this month by looking at all of the characteristics that you can define when creating a stored procedure. Here is a sample CREATE PROCEDURE statement making use of all of the clauses.

CREATE PROCEDURE sp_full() 

LANGUAGE SQL 

NOT DETERMINISTIC 

MODIFIES SQL DATA 

SQL SECURITY DEFINER 

COMMENT 'Returns a random number' 

SELECT RAND()

LANGUAGE SQL simply means that the language used to write the procedure is SQL. MySQL cannot yet use procedures in any other languages, but it probably will in the future. It is, unsurprisingly, the default.
NOT DETERMINISTIC implies that the procedure may produce different results, given the same inputs. The alternative is DETERMINISTIC, if the procedure always gives the same results given the same input. The default is NOT DETERMINISTIC. Procedures that are NOT DETERMINISTIC have implications for binary logging and replication (if it performs updates, it means that slave data could be different to master data, and for this reason you can't define a NOT DETERMINISTIC procedure if binary logging is enabled). Besides binary logging, MySQL as yet makes no use of this information (potentially the optimizer could use it to improve performance).
MODIFIES SQL DATA indicates that data could be modified by the procedure. Alternatives are CONTAINS SQL (which would be SQL that does not read or write data), NO SQL or READS SQL DATA. The default is CONTAINS SQL.
SQL SECURITY DEFINER indicates that MySQL should check the privileges of the user that defined the procedure when executing the procedure. The alternative is SQL SECURITY INVOKER, which causes MySQL to use the privileges of the user calling the procedure. The default is SQL SECURITY DEFINER.
COMMENT is self-explanatory, and can be used to describe the procedure. It is a non-standard MySQL extension.

SQL statements unique to Stored Procedures

A procedure may need to use conditions or loops, and standard SQL is not sufficient for these purposes. For that reason, the SQL standard includes a number of statements that only occur inside stored procedures. We already looked at the DECLARE statement last month. Let's take a look now at conditions and loops. The concepts should all be familiar to anyone with programming experience.

Conditions: IF THEN ELSE

MySQL implements conditions in the stock standard, tried and tested way.

IF condition 

THEN statement/s 

ELSE statement/s

END IF

The logic is simple. If the condition is met, then a set of statements are carried out. Else, another set of statements are carried out.
Here is an example:

mysql> CREATE PROCEDURE sp_condition(IN var1 INT) 

BEGIN 

 IF (var1 > 10)  

  THEN SELECT 'greater'; 

  ELSE SELECT 'less than or equal'; 

 END IF; 

END|

Query OK, 0 rows affected (0.06 sec)

mysql> CALL sp_condition(5)\G

*************************** 1. row ***************************

less than or equal: less than or equal

1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> CALL sp_condition(15)\G

*************************** 1. row ***************************

greater: greater

1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

I presume you can follow the logic easily enough. NULL values throw a curveball into the mix - an argument against their usage is that they undermine standard boolean logic. Nevertheless, you may come across instances where they are used. Let's see what happens if we pass a NULL to the procedure we have just created:

mysql> CALL sp_condition(NULL)\G

*************************** 1. row ***************************

less than or equal: less than or equal

1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

As you probably expected, NULL > 10 evaluates to false, so the ELSE statement is run. Let's switch things around:

mysql> CREATE PROCEDURE sp_condition2(IN var1 INT) 

BEGIN 

 IF (var1 <= 10) 

  THEN SELECT 'less than or equal'; 

  ELSE SELECT 'greater';

 END IF; 

END|

Query OK, 0 rows affected (0.00 sec)

mysql> CALL sp_condition2(NULL)\G

*************************** 1. row ***************************

greater: greater

1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Using what should in boolean logic be an identical test, we get a differing result. NULL <= 10 also evaluates to false. NULL's are beyond such dualistic thinking. This should be enough of a warning to be cautious of NULL's!

Conditions: CASE

The other construct used for condition is CASE.

CASE variable 

WHEN condition1 statement/s 

WHEN condition2 statement/s

ELSE statement/s

END CASE

This construct is used when the same variable is being tested against multiple conditions. Instead of long nested IF statements, using a CASE statement allows the procedure code to be more compact and easily readable. Here is an example:

mysql> CREATE PROCEDURE sp_case(IN var1 INT) 

BEGIN 

 CASE var1 

  WHEN 1 THEN SELECT 'One'; 

  WHEN 2 THEN SELECT 'Two'; 

  ELSE SELECT 'Something else'; 

 END CASE; 

END|

Query OK, 0 rows affected (0.00 sec)

mysql> CALL sp_case(1)\G

*************************** 1. row ***************************

One: One

1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> CALL sp_case(2)\G

*************************** 1. row ***************************

Two: Two

1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> CALL sp_case(3)\G

*************************** 1. row ***************************

Something else: Something else

1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Loops: WHILE

Loops are a vital component of procedures - they allow the same portion of code to be repeated a number of time. WHILE loops, the first type of loop we are going to look at, continuously repeat a block while a particular condition is true.

WHILE condition DO

statement/s

END WHILE

Here is an example. Be careful when entering it though, and with all loops! Always create your procedures on a test server first. All novice (and some not so novice) programmers at one time or another create an infinite loop. If you had forgotten to increment var1 in the procedure, it would always stay as whatever you had passed to the procedure. If this were anything less than 20, the loop would continue indefinitely.

mysql> CREATE PROCEDURE sp_while(IN var1 INT)

BEGIN 

 WHILE (var1 < 20) DO 

  SELECT var1; 

  SET var1=var1+1; 

 END WHILE;

END|

Query OK, 0 rows affected (0.00 sec)

mysql> CALL sp_while(18)\G

*************************** 1. row ***************************

var1: 18

1 row in set (0.00 sec)

*************************** 1. row ***************************

var1: 19

1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> CALL sp_while(22)\G

Query OK, 0 rows affected (0.00 sec)

Note that when we called the procedure, passing 22, no statements were executed, as the condition failed immediately.

REPEAT UNTIL

The other commonly used loop is the REPEAT UNTIL construct.

REPEAT 

statement/s

UNTIL condition

END REPEAT

The statements are repeatedly performed until the condition is met. A difference to the WHILE loop is that the condition is only tested after the statements have been performed, so there is always at least one instance of the statements being run. Here is an example:

mysql> CREATE PROCEDURE sp_repeat(IN VAR1 INT)

BEGIN 

 REPEAT 

  SELECT var1; 

  SET var1=var1+1; 

  UNTIL var1>5 

 END REPEAT; 

END|

Query OK, 0 rows affected (0.09 sec)

mysql> CALL sp_repeat(3)\G

*************************** 1. row ***************************

var1: 3

1 row in set (0.00 sec)

*************************** 1. row ***************************

var1: 4

1 row in set (0.00 sec)

*************************** 1. row ***************************

var1: 5

1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> CALL sp_repeat(8)\G

*************************** 1. row ***************************

var1: 8

1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Note, that even when we call the procedure with 8, which means that the condition evaluates to true (and exits the loop) the first time it is encountered, the SELECT statement is still executed once, as the condition is only tested at the end.

LABELS, LEAVES and LOOPS

Labels are simply text strings that are used to mark part of the procedure. They can simply play the role of comments, or be part of the logic, as we will see below in the LOOP construct. Here is an example of LABELS used to comment the start of a procedure. The label is begin1. This may not seem useful here, but complex statements can be deeply nested, and in that case, clear labels will make the logic easier to follow. They also play a vital logical role, as we will see later.

mysql> CREATE PROCEDURE sp_label() 

 begin1: BEGIN 

END|

Query OK, 0 rows affected (0.00 sec)

mysql> CREATE PROCEDURE sp_label2() 

 begin1: BEGIN 

 END begin1|

Query OK, 0 rows affected (0.00 sec)

In the second example, the end is also labeled. If an end label exists, it must match a start label of the same name. You can use LABELS before BEGIN, WHILE, REPEAT and LOOP (introduced below) statements, as well as the accompanying END statements, and also as targets for ITERATE statements (also introduced below).
A third kind of loop is the LOOP construct. This one does not test against a condition at either the start or the end of the loop. It continues looping until explicitly exited with a LEAVE statement, making it easy to get stuck in an infinite loop (as I did when creating this example). The LEAVE statement exits a block (which can include the procedure itself). Since there can be many nested constructs, it must also be accompanied by a label name to determine which block to leave.

label LOOP

 statement/s

 LEAVE label

 statement/s

END LOOP

Here is an example:

mysql> CREATE PROCEDURE sp_loop(IN var1 INT)

BEGIN

 loop1: LOOP

  IF (var1 > 5) THEN

   LEAVE loop1;

  END IF;

  SET var1=var1+1;

  SELECT var1;

 END LOOP;

END |

Query OK, 0 rows affected (0.00 sec)

mysql> CALL sp_loop(2)\G

*************************** 1. row ***************************

var1: 3

1 row in set (0.00 sec)

*************************** 1. row ***************************

var1: 4

1 row in set (0.00 sec)

*************************** 1. row ***************************

var1: 5

1 row in set (0.00 sec)

*************************** 1. row ***************************

var1: 6

1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

The LABEL name we used above is loop1, and the LEAVE statement explicitly left loop1. We did not make use of an end label.

ITERATE statements

Iterates can only appear within LOOP, REPEAT, and WHILE statements. They are followed by a LABEL name, and effectively direct control back to that LABEL. So, if they appear half-way through a loop, and they direct control back to the top of that loop, the rest of the loop will not be carried out (bear this logic in mind when testing, as it again opens the possibilities for infinite loops).
In this next example, we use a WHILE loop, and ITERATE back to the beginning of the loop if var1 is still less than 3.

mysql> CREATE PROCEDURE sp_while_iterate(IN var1 INT)

    -> BEGIN

    ->  while_label: WHILE (var1 < 5) DO

    ->   SELECT CONCAT('starting the loop: var1 is: ',var1);

    ->   SET var1=var1+1;

    ->   IF (var1<3) THEN

    ->    ITERATE while_label;

    ->   END IF;

    ->   SELECT CONCAT('ending the loop: var1 is: ',var1);

    ->  END WHILE;

    -> END|

Query OK, 0 rows affected (0.00 sec)

mysql> CALL sp_while_iterate(1)\G

*************************** 1. row ***************************

CONCAT('starting the loop: var1 is: ',var1): starting the loop: var1 is: 1

1 row in set (0.00 sec)

*************************** 1. row ***************************

CONCAT('starting the loop: var1 is: ',var1): starting the loop: var1 is: 2

1 row in set (0.00 sec)

*************************** 1. row ***************************

CONCAT('ending the loop: var1 is: ',var1): ending the loop: var1 is: 3

1 row in set (0.00 sec)

*************************** 1. row ***************************

CONCAT('starting the loop: var1 is: ',var1): starting the loop: var1 is: 3

1 row in set (0.00 sec)

*************************** 1. row ***************************

CONCAT('ending the loop: var1 is: ',var1): ending the loop: var1 is: 4

1 row in set (0.00 sec)

*************************** 1. row ***************************

CONCAT('starting the loop: var1 is: ',var1): starting the loop: var1 is: 4

1 row in set (0.00 sec)

*************************** 1. row ***************************

CONCAT('ending the loop: var1 is: ',var1): ending the loop: var1 is: 5

1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

The first time through the loop, only starting the loop is displayed, as var1 is at 2 when the time comes to evaluate the IF condition, and the condition evaluates to true. The ITERATE sends control back to the start of the loop again. The second time through, var1 starts at 2, but is then incremented to 3, and the IF condition is false. The full loop is executed.