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.
Part I: Stored Programming Fundamentals
Introduction to MySQL Stored Programs
MySQL Stored Programming Tutorial
Blocks, Conditional Statements, and Iterative Programming
Using SQL in Stored Programming
Part II: Stored Program Construction
Creating and Maintaining Stored Programs
MySQL Built-in Functions
Part III: Using MySQL Stored Programs in Applications
Using MySQL Stored Programs in Applications
Using MySQL Stored Programs with PHP
Using MySQL Stored Programs with Java
Using MySQL Stored Programs with Perl
Using MySQL Stored Programs with Python
Using MySQL Stored Programs with .NET
Part IV: Optimizing Stored Programs
Stored Program Security
Tuning Stored Programs and Their SQL
Basic SQL Tuning
Advanced SQL Tuning
Optimizing Stored Program Code
Best Practices in MySQL Stored Program Development