O'Reilly logo

MySQL Stored Procedure Programming by Steven Feuerstein, Guy Harrison

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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.

Conditional execution with the IF statement
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 ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required