Conditional Control

Conditional controlor "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.

4.2.1. 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;

 

4.2.1.1. 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 NULLneither 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;

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 valuesor strings that look like numbersmay 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.

4.2.1.2. 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.

For any nontrivial IF statement, use indenting and formatting to ensure that the logic of your IF statement is easily understood.

 

4.2.1.3. 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 againbecause it is importantthat NOT TRUE does not always mean FALSE. If the IF statement condition evaluates to NULL, then the ELSE 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;

4.2.1.4. 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 in Example 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 the sale_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 logicas 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;

4.2.2. 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.

4.2.2.1. Simple CASE statement

CASE statements can take two forms. The firstsometimes referred to as a simple CASE statementcompares 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.

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.

4.2.2.2. "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 programnot a happy outcome. So we should protect our codeand our job securityby 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 statementbut 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 ELSE statements. 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;

4.2.3. 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.

Rememberwhichever construct you usethat:

• 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 FALSEit could be NULL.
• You should think carefully about the readability of your statementssometimes 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).