Conditional Execution

You can control the flow of execution in your stored program by using IF or CASE statements. Both have roughly the same functionality; we will demonstrate the use of IF in this tutorial, as it's probably the most familiar of the two constructs.

Figure 2-8 shows a stored program that works out the discounted rate for a purchase based on the size of the purchase, and Example 2-5 shows its execution. Purchases over $500 get a 20% discount, while purchases over $100 get a 10% discount.

Figure 2-8. Conditional execution with the IF statement

 

Example 2-5. Creating and executing a stored procedure that contains an IF statement

mysql> SOURCEdiscounted_price.sql
Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> CALLdiscounted_price(300,@new_price) $$
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT@new_price$$
+------------+
| @new_price |
+------------+
| 270.0 |
+------------+
1 row in set (0.00 sec)

The IF statement allows you to test the truth of an expression such as normal_price > 500 and take appropriate action based on the result of the expression. As with other programming languages, the ELSEIF clause is used for all conditional branches after the initial IF. The ELSE clause is executed if the Boolean expressions in the IF and ELSEIF clauses all evaluate to false.

CASE has very similar functionality, and may be preferable when you are comparing a single expression against a set of possible distinct values. The two conditional statements are explored and contrasted in Chapter 4.