You can control the flow of execution in your stored
program by using
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.
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)
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
clause is used for all conditional branches after the initial
ELSE clause is executed if the Boolean
expressions in the
ELSEIF clauses all evaluate to false.
CASE has very similar functionality, and may be preferable when you ...