MySQL Stored Procedure Programming (2009)
Part I. Stored Programming Fundamentals
Chapter 4. Blocks, Conditional Statements, and Iterative Programming
This chapter describes the constructs in the MySQL language that control the scope and flow of execution.
In MySQL, as in all block-structured languages, groups of statements may be grouped together into blocks . A block can normally occur whenever a single statement would be permitted, and the block may contain its own distinct variable, cursor, and handler declarations.
The MySQL stored program language supports two types of stored program control statements: conditional control statements and iteration (looping) statements. Almost every piece of code you write requires conditional control, which is the ability to direct the flow of execution through your program based on a condition. You do this with IF-THEN-ELSE and CASE statements.
Iterative control structures—otherwise known as loops—let you execute the same code repeatedly. MySQL provides three different kinds of loop constructs:
Simple loop
Continues until you issue a LEAVE statement to terminate the loop
REPEAT UNTIL loop
Continues until an expression evaluates as true
WHILE loop
Continues as long as an expression evaluates as true
Block Structure of Stored Programs
Most MySQL stored programs consist of one or more blocks (the only exception is when a stored program contains only a single executable statement). Each block commences with a BEGIN statement and is terminated by an END statement. So in the simplest case, a stored program consists of a program definition statement (CREATE PROCEDURE, CREATE FUNCTION, or CREATE TRIGGER) followed by a single block that contains the program code to be executed:
CREATE {PROCEDURE|FUNCTION|TRIGGER} program_name
BEGIN
program_statements
END;
The purpose of a block is twofold:
To logically group related code segments
For instance, a handler declaration (see Chapter 6 for an explanation of error handlers) can include a block definition allowing it to execute multiple commands. All of the statements within the block will be executed if the handler is invoked.
To control the scope of variables and other objects
You can define a variable within a block that is not visible outside the block. Furthermore, you can declare a variable within a block that overrides the definition of a variable with the same name declared outside of the block.
Tip
A compound statement consists of a BEGIN-END block, which encloses one or more stored program commands.
Structure of a Block
A block consists of various types of declarations (e.g., variables, cursors, handlers) and program code (e.g., assignments, conditional statements, loops). The order in which these can occur is as follows:
1. Variable and condition declarations. Variables were discussed earlier in Chapter 3, and condition declarations are discussed in Chapter 6.
2. Cursor declarations, discussed in Chapter 5.
3. Handler declarations, discussed in Chapter 6.
4. Program code.
If you violate this order—for instance, by issuing a DECLARE statement after a SET statement—MySQL will generate an error message when you try to create your stored program code. The error messages do not always clearly indicate that you have used statements in the wrong order, so it's important to develop the habit of declaring things in the correct order.
Tip
The order of statements in a block must be Variables and conditions, followed by Cursors, then Exception handlers, and finally Other statements. We remember this order using the following mnemonic: "Very Carefully Establish Order" in your stored programs.
You can also name a block with a label. The label can occur both before the BEGIN statement and after the END statement. Labeling a block has the following advantages:
§ It improves code readability—for instance, by allowing you to quickly match the BEGIN statement with its associated END statement.
§ It allows you to terminate block execution with the LEAVE statement (see the section describing this statement later in this chapter).
So a simplified representation of the structure of a block is:
[label:] BEGIN
variable and condition declarations]
cursor declarations
handler declarations
program code
END[label];
Nested Blocks
If all stored programs contained only a single block, the block structure would be hardly worth mentioning. However, many programs include blocks that are defined within an enclosing block—at least within the main block that encloses all the stored program code. As suggested earlier, variables declared within a block are not available outside the block, but may be visible to blocks that are declared within the block. You can override an "outer" variable with a new definition within the block, and you can manipulate this variable without affecting the value of the "outer" variable.
Let's illustrate some of these principles with some examples.
In Example 4-1, we create a variable within a block. The variable is not available in the outer block, so this example generates an error.
Example 4-1. Declarations within a block are not visible outside the block
mysql> CREATE PROCEDURE nested_blocks1( )
BEGIN
DECLARE outer_variable VARCHAR(20);
BEGIN
DECLARE inner_variable VARCHAR(20);
SET inner_variable='This is my private data';
END;
SELECT inner_variable,' This statement causes an error ';
END;
$$
Query OK, 0 rows affected (0.00 sec)
mysql> CALL nested_blocks1( )
--------------
ERROR 1054 (42S22): Unknown column 'inner_variable' in 'field list'
In Example 4-2, we modify a variable declared in the "outer" block inside of an "inner" block. The changes made are visible outside of the inner block.
Example 4-2. Variables within a block can override variables defined outside the block
mysql> CREATE PROCEDURE nested_blocks2( )
BEGIN
DECLARE my_variable varchar(20);
SET my_variable='This value was set in the outer block';
BEGIN
SET my_variable='This value was set in the inner block';
END;
SELECT my_variable, 'Changes in the inner block are visible in the outer block';
END;
$$
Query OK, 0 rows affected (0.00 sec)
mysql> CALL nested_blocks2( )
//
+---------------------+-----------------------------------------------------------+
| my_variable | Changes in the inner block are visible in the outer block |
+---------------------+-----------------------------------------------------------+
| This value was set | |
| in the inner block | Changes in the inner block are visible in the outer block |
+---------------------+-----------------------------------------------------------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.01 sec)
In Example 4-3, we create a variable in the inner block with the same name as one in the outer block. When we change the value within the inner block, the changes are not reflected in the outer block—that's because although the two variables have the same name, they are really two separate variables. Overriding a variable name inside of a block in this way can be fairly confusing, reducing code readability and possibly encouraging bugs. In general, don't override variable definitions in this way unless you have a very compelling reason.
Example 4-3. Changes made to an overloaded variable in an inner block are not visible outside the block
mysql> CREATE PROCEDURE nested_blocks3( )
BEGIN
DECLARE my_variable varchar(20);
SET my_variable='This value was set in the outer block';
BEGIN
DECLARE my_variable VARCHAR(20);
SET my_variable='This value was set in the inner block';
END;
SELECT my_variable, 'Can''t see changes made in the inner block';
END;
//
Query OK, 0 rows affected (0.00 sec)
mysql> CALL nested_blocks3( )
$$
+---------------------------+-------------------------------------------+
| my_variable | Can't see changes made in the inner block |
+---------------------------+-------------------------------------------+
| This value was set in the | |
| outer block | Can't see changes made in the inner block |
+---------------------------+-------------------------------------------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Tip
Avoid overriding a variable declared within an outer block inside an inner block.
In our final nested blocks example (Example 4-4), we use a block label and the LEAVE statement to terminate block execution. We discuss the use of the LEAVE statement later in this chapter, but for now it's enough to point out that you can terminate execution of a block with a LEAVEstatement at any time, providing that the block is labeled.
Example 4-4. Example of using a LEAVE statement to exit a labeled block
mysql> CREATE PROCEDURE nested_blocks5( )
outer_block: BEGIN
DECLARE l_status int;
SET l_status=1;
inner_block: BEGIN
IF (l_status=1) THEN
LEAVE inner_block;
END IF;
SELECT 'This statement will never be executed';
END inner_block;
SELECT 'End of program';
END outer_block$$
Query OK, 0 rows affected (0.00 sec)
mysql> CALL nested_blocks5( )$$
+----------------+
| End of program |
+----------------+
| End of program |
+----------------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Conditional Control
Conditional control—or "flow of control"—statements allow you to execute code based on the value of some expression. As we said earlier, an expression can be any combination of MySQL literals, variables, operators, and functions that returns a value. Conditional control statements allow you to take different actions depending on the value of such an expression, which could refer to parameters to the stored program, to data in the database, or to other variable data (such as the day of the week or the time of the day).
The MySQL stored program language supports two conditional control statements : IF and CASE. Both IF and CASE perform very similar functions, and there is always a way to rewrite an IF statement as a CASE statement or vice versa. Usually, choosing between IF and CASE is a matter of personal preference or programming standards. However, there are circumstances in which one type of statement is more readable or efficient than the other.
The following subsections describe the syntax of both statements, provide usage examples, and, finally, compare the pros and cons of each.
The IF Statement
All programmers will be familiar with some variation of the IF statement, and MySQL's implementation of the IF statement contains no surprises. The syntax of IF in stored programs is:
IF expression THEN commands
[ELSEIF expression THEN commands ....]
[ELSE commands]
END IF;
TRUE or FALSE (or neither)?
The commands associated with IF or ELSEIF statements will only be executed if the associated expression evaluates to TRUE. Expressions such as 1=1 or 2>1 will evaluate to TRUE. Expressions such as 1>3 will evaluate to FALSE.
However, if you are performing an operation on one or more variables, and one of the variables has a NULL value, then the result of the expression can be NULL—neither TRUE nor FALSE. This can lead to some erroneous conclusions if your code assumes that expressions that are not TRUE are necessarily FALSE, or vice versa. So, for instance, in Example 4-5, if we can't find 'alpha' or 'beta' in the version string, we assume that the release is production. However, if l_version is NULL, then the ELSE condition will always fire, although we actually have no basis for making any such assertion.
Example 4-5. Incorrectly assuming that NOT TRUE = FALSE
IF (INSTR(l_version_string,'alpha')>0) THEN
SELECT 'Alpha release of MySQL';
ELSEIF (INSTR(l_version_string,'beta')>0) THEN
SELECT 'Beta release of MySQL';
ELSE
SELECT 'Production release of MySQL';
END IF;
Tip
Don't assume that the result of an expression is either TRUE or FALSE. It could also evaluate to NULL (UNKNOWN) if any of the participating variables is NULL.
Also note that any expressions that return numeric values—or strings that look like numbers—may evaluate to TRUE, FALSE, or NULL. The rules are:
§ If the absolute value of a numeric expression is 1 or greater, then it will be evaluated to TRUE by the IF or ELSEIF statement. Note that the term "absolute value" means that both 1 and -1 will evaluate to TRUE.
§ If the value of the numeric expression is 0, then it will evaluate to FALSE.
Simple IF-THEN combinations
In its simplest form, IF can be used to specify a set of statements that executes only if a condition evaluates to TRUE. The syntax for this type of IF statement is as follows:
IF expression THEN
statements
END IF;
Three-Valued Logic
Boolean expressions can return three possible results. When all values in a Boolean expression are known, the result is either TRUE or FALSE. For example, there is no doubt when determining the truth or falsity of an expression such as:
(2 < 3) AND (5 < 10)
Sometimes, however, you don't know all values in an expression. That's because databases allow for values to be NULL, or missing. What, then, can be the result from an expression involving NULLs? For example:
2 < NULL
Because you don't know what the missing value is, the only answer you can give is "I don't know." This is the essence of so-called three-valued logic, that you can have not only TRUE and FALSE as a possible result, but also NULL.
To learn more about three-valued logic, we recommend C. J. Date's book Database In Depth: Relational Theory for the Practitioner (O'Reilly).
Example 4-6 shows a simple IF statement.
Example 4-6. Example of simple IF statement
IF sale_value > 200 THEN
CALL apply_free_shipping(sale_id);
END IF
;
We can include multiple statements between the THEN and END IF clauses, as in Example 4-7.
Example 4-7. Multistatement IF statement
IF sale_value > 200 THEN
CALL apply_free_shipping(sale_id);
CALL apply_discount(sale_id,10);
END IF;
As shown in Example 4-8, we can also include any other executable statement inside the IF statement, such as looping constructs, SET statements, and other IF statements (although, as we will see later, it's often best to avoid nesting IF statements in this manner if possible).
Example 4-8. Nested IF statements
IF sale_value > 200 THEN
CALL apply_free_shipping(sale_id);
IF sale_value > 500 THEN
CALL apply_discount(sale_id,20);
END IF;
END IF;
It is not necessary to break the IF statement across multiple lines; all of the IF statements in Example 4-9 are treated identically by MySQL.
Example 4-9. Alternate formatting for IF statements
IF sale_value > 200 THEN CALL apply_free_shipping(sale_id); END IF;
IF sale_value > 200
THEN
CALL apply_free_shipping(sale_id);
END IF;
IF sale_value > 200 THEN
CALL apply_free_shipping(sale_id);
END IF;
It's probably OK to put a very simple IF statement on a single line, but it is definitely not a good practice to do this for complex or nested IF structures. For instance, which is easier to read, understand, and maintain? This:
IF sale_value > 200 THEN
CALL apply_free_shipping(sale_id);
IF sale_value > 500 THEN
CALL apply_discount(sale_id,20);
END IF;
END IF;
Or this:
IF sale_value > 200 THEN CALL apply_free_shipping(sale_id); IF sale_value >
500 THEN CALL apply_discount(sale_id,20);END IF;END IF;
Some programmers like to place the THEN clause on a separate line, as follows:
IF sale_value > 200
THEN
CALL apply_free_shipping(sale_id);
END IF;
But this is really a matter of personal preference and/or programming standards.
Tip
For any nontrivial IF statement, use indenting and formatting to ensure that the logic of your IF statement is easily understood.
IF-THEN-ELSE statements
Adding an ELSE condition to your IF statements allows you to specify statements that will execute if the IF condition is NOT TRUE. We'll emphasize again—because it is important—that NOT TRUE does not always mean FALSE. If the IF statement condition evaluates to NULL, then theELSE statements will still be executed; this can lead to subtle bugs if you don't protect against NULL variables in your IF conditions.
An IF-THEN-ELSE block has the following syntax:
IF expression THEN
statements that execute if the expression is TRUE
ELSE
statements that execute if the expression is FALSE or NULL
END IF;
So in Example 4-10, we apply shipping to an order if it is less than $200; otherwise, we apply a discount (and don't charge shipping).
Example 4-10. Simple IF-THEN ELSE example
IF sale_value <200 THEN
CALL apply_shipping(sale_id);
ELSE
CALL apply_discount(sale_id);
END IF;
IF-THEN-ELSEIF-ELSE statements
The full syntax of the IF statements allows for multiple conditions to be defined. The first condition that evaluates to TRUE will execute. If none of the statements evaluates to TRUE, then the ELSE clause (if present) will execute. The syntax for an IF-THEN-ELSEIF-ELSE IF statement looks like this:
IF expression THEN
statements that execute if the expression is TRUE
ELSEIF expression THEN
statements that execute if expression1 is TRUE
ELSE
statements that execute if all the preceding expressions are FALSE or NULL
END IF;
You can have as many ELSEIF conditions as you like.
The conditions do not need to be mutually exclusive. That is, more than one of the conditions can evaluate to TRUE. The first condition that evaluates to TRUE is the one that executes. Creating overlapping conditions like this can be useful, but you have to be very careful when ordering the conditions. For instance, consider the IF-ELSEIF statement shown in Example 4-11.
Example 4-11. Example of an IF-ELSEIF block with overlapping conditions
IF (sale_value>200) THEN
CALL free_shipping(sale_id);
ELSEIF (sale_value >200 and customer_status='PREFERRED') THEN
CALL free_shipping(sale_id);
CALL apply_discount(sale_id,20);
END IF;
The intention of this code fragment is clear: apply free shipping to all orders over $200, and add a 20% discount for preferred customers. However, because the first condition will evaluate to TRUE for all orders over $200, the ELSEIF condition will not be evaluated for any orders over $200, and our preferred customers will not get their discount. No discount for preferred customers means no end-of-year bonus for our stored procedure programmer!
There are a number of better ways to craft this statement: for one thing, we could move the ELSEIF condition into the IF clause to ensure that it gets evaluated first; alternately, we could nest an IF statement within the sale_value>200 IF clause to test the customer status, as shown inExample 4-12.
Example 4-12. Two ways of correcting the logic error in the previous example
/* Reordering the IF conditions */
IF (sale_value >200 and customer_status='PREFERED') THEN
CALL free_shipping(sale_id);
CALL apply_discount(sale_id,20);
ELSEIF (sale_value>200) THEN
CALL free_shipping(sale_id);
END IF;
/* Nesting the IF conditions */
IF (sale_value >200) THEN
CALL free_shipping(sale_id);
IF (customer_satus='PREFERRED') THEN
CALL apply_discount(sale_id,20);
END IF;
END IF:
Both of the alternatives shown in Example 4-12 are perfectly valid. Generally we want to avoid nesting IF statements where possible, but if there are a lot of additional evaluations that we need to conduct when the sale_value is greater than $200, then it might make sense to perform thesale_value test once, and then individually test for all the other conditions. So let's say our business rules state that for orders over $200 we give free shipping, along with a variable discount based on the customer's status in our loyalty program. The logic in a single IF-ELSEIF block might look like that shown in Example 4-13.
Example 4-13. IF block with many redundant conditions
IF (sale_value >200 and customer_status='PLATINUM') THEN
CALL free_shipping(sale_id); /* Free shipping*/
CALL apply_discount(sale_id,20); /* 20% discount */
ELSEIF (sale_value >200 and customer_status='GOLD') THEN
CALL free_shipping(sale_id); /* Free shipping*/
CALL apply_discount(sale_id,15); /* 15% discount */
ELSEIF (sale_value >200 and customer_status='SILVER') THEN
CALL free_shipping(sale_id); /* Free shipping*/
CALL apply_discount(sale_id,10); /* 10% discount */
ELSEIF (sale_value >200 and customer_status='BRONZE') THEN
CALL free_shipping(sale_id); /* Free shipping*/
CALL apply_discount(sale_id,5); /* 5% discount*/
ELSEIF (sale_value>200) THEN
CALL free_shipping(sale_id); /* Free shipping*/
END IF;
In this case, the constant repetition of the sale_value condition and the free_shipping call actually undermines the readability of our logic—as well as imposing a performance overhead (see Chapter 22). It might be better to use a nested IF structure that makes it clear that everyone gets free shipping for orders over $200, and that discounts are then applied based on the customer loyalty status only. Example 4-14 shows the nested IF implementation.
Example 4-14. Using nested IF to avoid redundant evaluations
IF (sale_value > 200) THEN
CALL free_shipping(sale_id); /*Free shipping*/
IF (customer_status='PLATINUM') THEN
CALL apply_discount(sale_id,20); /* 20% discount */
ELSEIF (customer_status='GOLD') THEN
CALL apply_discount(sale_id,15); /* 15% discount */
ELSEIF (customer_status='SILVER') THEN
CALL apply_discount(sale_id,10); /* 10% discount */
ELSEIF (customer_status='BRONZE') THEN
CALL apply_discount(sale_id,5); /* 5% discount*/
END IF;
END IF;
The CASE Statement
The CASE statement is an alternative conditional execution or flow control statement. Anything that can be done with CASE statements can be done with IF statements (and vice versa), but CASE statements are often more readable and efficient when multiple conditions need to be evaluated, especially when the conditions all compare the output from a single expression.
Simple CASE statement
CASE statements can take two forms. The first—sometimes referred to as a simple CASE statement—compares the output of an expression with multiple conditions:
CASE expression
WHEN value THEN
statements
[WHEN value THEN
statements ...]
[ELSE
statements]
END CASE;
This syntax is useful when we are checking the output of some expression against a set of distinct values. For instance, we could check the customer loyalty status from our previous example using the simple CASE statement shown in Example 4-15.
Example 4-15. Example of a simple CASE statement
CASE customer_status
WHEN 'PLATINUM' THEN
CALL apply_discount(sale_id,20); /* 20% discount */
WHEN 'GOLD' THEN
CALL apply_discount(sale_id,15); /* 15% discount */
WHEN 'SILVER' THEN
CALL apply_discount(sale_id,10); /* 10% discount */
WHEN 'BRONZE' THEN
CALL apply_discount(sale_id,5); /* 5% discount*/
END CASE;
As with the IF command, you can specify multiple WHEN statements and you can specify an ELSE clause that executes if none of the other conditions apply.
However, it is critical to realize that a CASE statement will raise an exception if none of the conditions apply. This means that in Example 4-15 if the customer_status was not one of 'PLATINUM', 'GOLD', 'SILVER', or 'BRONZE' then the following runtime exception would occur:
ERROR 1339 (20000): Case not found for CASE statement
We could create an exception handler to cause this error to be ignored (as described in Chapter 6), but it is probably better practice to code an ELSE clause to ensure that all possible conditions are handled. So, we should probably adapt the previous example to include an ELSE clause that applies a zero discount to a customer who meets none of the preceding conditions.
Tip
If none of the CASE statements matches the input condition, CASE will raise MySQL error 1339. You should either construct an error handler to ignore this error, or ensure that the exception never occurs by including an ELSE clause in your CASE statement.
The simple CASE statement is useful when comparing the value of an expression to a series of specific values. However, the simple CASE statement cannot easily or naturally match ranges, or handle more complex conditions involving multiple expressions. For these more complex "cases" we can use a "searched" CASE statement, described in the next section.
"Searched" CASE statement
The searched CASE statement is functionally equivalent to an IF-ELSEIF-ELSE-END IF block. The searched CASE statement has the following syntax:
CASE
WHEN condition THEN
statements
[WHEN condition THEN
statements...]
[ELSE
statements]
END CASE;
Using the searched CASE structure, we can implement the free shipping and discount logic that we implemented earlier using IF. A direct translation of our sales discount and free shipping logic using a searched CASE statement is shown in Example 4-16.
Example 4-16. Example of a searched CASE statement
CASE
WHEN (sale_value >200 AND customer_status='PLATINUM') THEN
CALL free_shipping(sale_id); /* Free shipping*/
CALL apply_discount(sale_id,20); /* 20% discount */
WHEN (sale_value >200 AND customer_status='GOLD') THEN
CALL free_shipping(sale_id); /* Free shipping*/
CALL apply_discount(sale_id,15); /* 15% discount */
WHEN (sale_value >200 AND customer_status='SILVER') THEN
CALL free_shipping(sale_id); /* Free shipping*/
CALL apply_discount(sale_id,10); /* 10% discount */
WHEN (sale_value >200 AND customer_status='BRONZE') THEN
CALL free_shipping(sale_id); /* Free shipping*/
CALL apply_discount(sale_id,5); /* 5% discount*/
WHEN (sale_value>200) THEN
CALL free_shipping(sale_id); /* Free shipping*/
END CASE;
However, remember that if none of the WHERE clauses is matched, a 1339 error will occur. Therefore, this code will cause a fatal error if the order is less than $200 or the customer is not in our loyalty program—not a happy outcome. So we should protect our code—and our job security—by including an ELSE clause as shown in Example 4-17.
Example 4-17. Adding a dummy ELSE clause to our searched CASE example
CASE
WHEN (sale_value >200 AND customer_status='PLATINUM') THEN
CALL free_shipping(sale_id); /* Free shipping*/
CALL apply_discount(sale_id,20); /* 20% discount */
WHEN (sale_value >200 AND customer_status='GOLD') THEN
CALL free_shipping(sale_id); /* Free shipping*/
CALL apply_discount(sale_id,15); /* 15% discount */
WHEN (sale_value >200 AND customer_status='SILVER') THEN
CALL free_shipping(sale_id); /* Free shipping*/
CALL apply_discount(sale_id,10); /* 10% discount */
WHEN (sale_value >200 AND customer_status='BRONZE') THEN
CALL free_shipping(sale_id); /* Free shipping*/
CALL apply_discount(sale_id,5); /* 5% discount*/
WHEN (sale_value>200) THEN
CALL free_shipping(sale_id); /* Free shipping*/
ELSE
SET dummy=dummy;
END CASE;
Note that because MySQL lacks a NULL (do nothing) statement in the stored program language, we had to add a dummy statement—but this statement has negligible overhead .
As with our IF implementation of this logic, we could also use nested CASE statements to perform the same logic with arguably greater clarity. In Example 4-18 we combine simple and searched CASE statements, and also include a "not found" handler to avoid having to include ELSEstatements. We enclose the entire thing in a block so that our handler does not inadvertently influence other statements within the stored program.
Example 4-18. Using nested CASE statements and a block-scoped "not found" handler
BEGIN
DECLARE not_found INT DEFAULT 0;
DECLARE CONTINUE HANDLER FOR 1339 SET not_found=1;
CASE
WHEN (sale_value>200) THEN
CALL free_shipping(sale_id);
CASE customer_status
WHEN 'PLATINUM' THEN
CALL apply_discount(sale_id,20);
WHEN 'GOLD' THEN
CALL apply_discount(sale_id,15);
WHEN 'SILVER' THEN
CALL apply_discount(sale_id,10);
WHEN 'BRONZE' THEN
CALL apply_discount(sale_id,5);
END CASE;
END CASE;
END;
IF Versus CASE
We've seen that both IF and CASE statements can implement the same flow control functionality. So which is best? To a large extent, choosing between IF and CASE is more a matter of personal preference and programming standards than of any implicit advantages offered by either of the two statements. However, when deciding between CASE and IF, consider the following:
§ Consistency in style is probably more important than any slight advantages either approach might have in a particular circumstance. We therefore suggest that you choose between CASE and IF consistently, and not randomly switch between the two depending on your mood, the weather, or your horoscope!
§ CASE is slightly more readable when you are comparing a single expression against a range of distinct values (using a "simple" CASE statement).
§ IF is probably a more familiar and easily understood construct when you are evaluating ranges or complex expressions based on multiple variables.
§ If you choose CASE, you need to ensure that at least one of the CASE conditions is matched, or define an error handler to catch the error that will occur if no CASE condition is satisfied. IF has no such restriction.
Remember—whichever construct you use—that:
§ Once any condition in the CASE or IF structure is satisfied, no more conditions will be evaluated. This means that if your conditions overlap in any way, the order of evaluation is critical.
§ The MySQL stored program language uses three-valued logic; just because a statement is NOT TRUE does not mean that it is necessary FALSE—it could be NULL.
§ You should think carefully about the readability of your statements—sometimes a nested set of IF or CASE statements will be more readable and possibly more efficient. However, more often it is better to avoid nesting, especially if the statements become deeply nested (say three or more levels).
Iterative Processing with Loops
In this section we examine the statements that the MySQL stored program language provides for iteratively (repeatedly) processing commands. There are many reasons why a program may need to iterate:
§ A program that supports a user interface may run a main loop that waits for, and then processes, user keystrokes (this doesn't apply to stored programs, however).
§ Many mathematical algorithms can be implemented only by loops in computer programs.
§ When processing a file, a program may loop through each record in the file and perform computations.
§ A database program may loop through the rows returned by a SELECT statement.
It's fairly obvious that it is the last case—processing rows returned by a SELECT statement—that will be the most common reason for looping in MySQL stored programs, and we will give this topic a great deal of consideration in Chapter 5. In this chapter, we consider the looping commands in their general form.
LOOP Statement
The simplest possible looping construct is the LOOP statement. The syntax for this statement is as follows:
[label:] LOOP
statements
END LOOP
[label];
The statements between the LOOP and END LOOP statements will be repeated indefinitely, until the LOOP is terminated. You can terminate the LOOP using the LEAVE statement, which we will describe shortly.
You can supply labels to the loop, which have the same syntax as those we can add to BEGIN-END blocks. Labels can help you identify the END LOOP statement that corresponds to a particular LOOP statement. Equally important, labels can be used to control execution flow, as we will see in subsequent sections.
Example 4-19 shows a very simple (and very dangerous) loop. It will continue forever, or at least until you manage to somehow terminate it. Because stored programs run inside of the database server, using Ctrl-C or other forms of keyboard interrupts will be ineffective—you will only be able to terminate this loop by issuing a KILL command against the MySQL session, or by shutting down the database server. In the meantime, the loop will consume as much CPU as it can, so we don't recommend that you run this example on your mission-critical production systems.
Example 4-19. Infinite loop (don't try this at home!)
Infinite_loop: LOOP
SELECT 'Welcome to my infinite
loop from hell!!';
END LOOP inifinite_loop;
Obviously we almost never want to program an infinite loop, and therefore we need some way to terminate the loop. We can do this with the LEAVE statement, so let's move on to this statement without delay....
LEAVE Statement
The LEAVE statement allows us to terminate a loop. The general syntax for the LEAVE statement is:
LEAVE label;
LEAVE causes the current loop to be terminated. The label matches the loop to be terminated, so if a loop is enclosed within another loop, we can break out of both loops with a single statement.
In the simplest case, we simply execute LEAVE when we are ready to exit from the LOOP, as shown in Example 4-20.
Example 4-20. Using LEAVE to terminate a loop
SET i=1;
myloop: LOOP
SET i=i+1;
IF i=10 then
LEAVE myloop;
END IF;
END LOOP myloop;
SELECT 'I can count to 10';
LEAVE can be used to exit from any of the alternative looping structures, as we'll examine in upcoming sections. In fact, you can also use LEAVE if you want to break out of a named BEGIN-END block (introduced earlier in this chapter).
ITERATE Statement
The ITERATE statement is used to restart execution at the beginning of a loop, without executing any of the remaining statements in the loop. ITERATE has the following syntax:
ITERATE label;
When MySQL encounters the ITERATE statement, it recommences execution at the start of the nominated loop. In Example 4-21, we print all odd numbers less than 10. ITERATE is used to repeat the loop if the number we have is not odd. LEAVE is used to terminate the loop once we reach 10.
Example 4-21. Using ITERATE to return to the start of a loop
SET i=0;
loop1: LOOP
SET i=i+1;
IF i>=10 THEN /*Last number - exit loop*/
LEAVE loop1;
ELSEIF MOD(i,2)=0 THEN /*Even number - try again*/
ITERATE loop1;
END IF;
SELECT CONCAT(i," is an odd number");
END LOOP loop1;
While this loop is useful to illustrate the use of LEAVE and ITERATE to control a loop, it is a rather poorly constructed algorithm. We could easily have halved the number of loop iterations by incrementing the loop variable i by two rather than by one.
ITERATE causes the execution of the loop to restart at the top of the loop. If you are using a REPEAT loop (see the next section), this means that the loop will re-execute unconditionally, bypassing the UNTIL condition that would otherwise terminate the loop. This may result in unexpected behavior. In a WHILE loop, ITERATE will result in the WHILE condition being re-evaluated before the next iteration of the loop.
We can construct just about any conceivable form of loop using the LOOP, LEAVE, and ITERATE statements. However, in practice these "manual" loops are awkward when compared to some of the alternatives we are about to consider. The WHILE and REPEAT statements described in the following sections allow us to create loops that are easier to write, read, and maintain.
REPEAT ... UNTIL Loop
The REPEAT and UNTIL statements can be used to create a loop that continues until some logical condition is met. The syntax for REPEAT...UNTIL is:
[label:] REPEAT
statements
UNTIL expression
END REPEAT [label]
A REPEAT loop continues until the expression defined in the UNTIL clause evaluates to TRUE. In essence, a REPEAT loop is logically equivalent to a LOOP-LEAVE-END LOOP block like this one:
some_label:LOOP
statements
IF expression THEN LEAVE some_label; END IF;
END LOOP;
The REPEAT loop is somewhat easier to maintain because it is more obvious which conditions will cause the loop to terminate. The LEAVE statement in a simple loop could be anywhere, while the UNTIL statement is always associated with the END REPEAT clause at the very end of the loop. Furthermore, we don't need to specify a label for the REPEAT loop since the UNTIL condition is always specific to the current loop. However, we still recommend using labels with REPEAT loops to improve readability, especially if the loops are nested.
Example 4-22 shows using REPEAT to print out odd numbers less than 10. Compare this syntax with that of our previous example using the LOOP and LEAVE statements.
Example 4-22. Example of a REPEAT loop
SET i=0;
loop1: REPEAT
SET i=i+1;
IF MOD(i,2)<>0 THEN /*Even number - try again*/
Select concat(i," is an odd number");
END IF;
UNTIL i >= 10
END REPEAT;
There are a few things worth noting about the REPEAT loop:
§ A REPEAT loop is always guaranteed to run at least once—that is, the UNTIL condition is first evaluated after the first execution of the loop. For loops that should not run even once unless some condition is satisfied, use WHILE (see the next section).
§ Using ITERATE in a REPEAT loop can lead to unexpected outcomes, since doing so bypasses the UNTIL test and may result in the loop executing even though the UNTIL condition is no longer satisfied. Therefore, you will probably not want to use ITERATE in a REPEAT loop.
WHILE Loop
A WHILE loop executes as long as a condition is true. If the condition is not true to begin with, then the loop will never execute—unlike the REPEAT loop, which is guaranteed to execute at least once.
The WHILE loop has the following syntax:
[label:] WHILE expression DO
statements
END WHILE [label]
A WHILE loop is functionally equivalent to a simple LOOP-LEAVE-END LOOP construction that has a LEAVE clause as its very first statement, as described in the "LEAVE Statement" section. Example 4-23 demonstrates the LOOP-LEAVE-END-LOOP.
Example 4-23. LOOP-END LOOP that implements same functionality as WHILE loop
myloop: LOOP
IF expression THEN LEAVE myloop; END IF;
other statements;
END LOOP myloop;
Example 4-24 shows our odd-numbers-less-than-10 loop implemented using WHILE.
Example 4-24. Odd numbers less than 10 implemented as a WHILE loop
SET i=1;
loop1: WHILE i<=10 DO
IF MOD(i,2)<>0 THEN /*Even number - try again*/
SELECT CONCAT(i," is an odd number");
END IF;
SET i=i+1;
END WHILE loop1;
Nested Loops
We often want to nest loops. In the simple code in Example 4-25, we print out the elementary "times table" using a nested LOOP-LEAVE-END LOOP structure.
Example 4-25. Example of nesting loops
DECLARE i,j INT DEFAULT 1;
outer_loop: LOOP
SET j=1;
inner_loop: LOOP
SELECT concat(i," times ", j," is ",i*j);
SET j=j+1;
IF j>12 THEN
LEAVE inner_loop;
END IF;
END LOOP inner_loop;
SET i=i+1;
IF i>12 THEN
LEAVE outer_loop;
END IF;
END LOOP outer_loop;
When nesting loops, it is particularly useful to label the start and the end of the loop so as to clearly associate the start of each loop with its end. Of course, if we need to use LEAVE, we must label the loop.
Parting Comments on Loops
We've now seen three simple and identical looping algorithms implemented using the three looping constructs available within the MySQL stored program language. Each of the three loop constructs is capable of implementing virtually any loop logic that you might need to implement.
The example loops given in this chapter are fairly simplistic and have little real-world relevance. We did this partially for the sake of clarity, but also because the reality is that in stored programming, almost all your looping constructs will involve iterating through the rows returned by aSELECT statement, which is the subject of the next chapter.
Conclusion
In this chapter we looked at conditional and iterative control structures in the MySQL stored program language. Almost any nontrivial program will need to make some kind of decision based on input data, and these decisions will usually be expressed as IF or CASE statements.
Looping is another extremely common programming task—especially common in stored programs that need to iterate through the outputs from some SQL statement. MySQL provides a number of alternative ways to format a loop, including a simple loop terminated by a LEAVE statement, aREPEAT UNTIL loop, and a WHILE loop.