Chapter 11: Control Flow Tools
Now that we know how to write basic stored routines, let us move on to a slightly more advanced concept.
In the previous chapter, we looked at a simple function for calculating the bonuses of employees. What if we want to perform more complex calculations?
For instance, suppose we want to pay 2 months bonus for employees with salary below 3000, but 1 month bonus for employees with salary above that?
In order to achieve the above, we need to use control flow tools. These include IF, CASE, and LOOP statements.
In this chapter, we’ll be using a lot of examples to illustrate the concepts discussed. For each example, I’ll leave a line before and after the control flow statements so that you can see clearly where they start and end.
Ready?
IF statement
Let’s start with the IF statement. The syntax for the IF statement is as follows:
IF condition 1 is met THEN do task A;
ELSEIF condition 2 is met THEN do task B;
ELSEIF condition 3 is met THEN do task C;
…
ELSE do task Z;
END IF;
The IF statement first checks if the first condition is met. If it is, it’ll perform task A. If it is not, it’ll move on to the first ELSEIF statement. If this condition is met, it’ll perform task B. If it is not, it’ll move down the ELSEIF statements until it finds a condition that is met. If no conditions are met, it’ll perform task Z.
There can be as many ELSEIF statements as needed.
In addition, both the ELSEIF and ELSE statements are optional. You do not need to include them if there are no other conditions to check.
However, if you omit the ELSE statement and there exists a case that is not fulfilled by any of the IF and ELSEIF statements...
CASE statement
Next, let’s move on to CASE statements.
The CASE statement is very similar to the IF statement and can often be used interchangeably. In most cases, choosing between IF and CASE is a matter of personal preference.
The syntax is:
CASE case_variable
WHEN value_1 THEN do task A;
WHEN value_2 THEN do task B;
...
ELSE do task Z;
END CASE;
Or
CASE
WHEN condition 1 is met THEN do task A;
WHEN condition 2 is met THEN do task B;
...
ELSE do task Z;
END CASE;
The first syntax allows you to match the value of a variable against a set of distinct values. The second syntax allows you to perform more complex matches such as matching using ranges.
Let’s look at some examples.
Example 1
DELIMITER $$
CREATE FUNCTION case_demo_A(x INT) RETURNS VARCHAR(255) DETERMINISTIC
BEGIN
CASE x
WHEN 1 THEN RETURN 'x is 1';
&...
WHILE statement
The next control flow statement is the WHILE statement. This statement allows us to specify a task to be done repeatedly while a certain condition is valid.
The syntax is:
[name of while statement : ] WHILE condition is true DO
-- some tasks
END WHILE;
Example
DELIMITER $$
CREATE FUNCTION while_demo(x INT, y INT) RETURNS VARCHAR(255) DETERMINISTIC
BEGIN
DECLARE z VARCHAR(255);
SET z = '';
while_example: WHILE x<y DO
SET x = x + 1;
SET z = concat(z, x);
END WHILE;
RETURN z;
END $$
DELIMITER ;
Here, we first declare a local variable z and initialize it to an empty string (an empty string is a string with no content).
Next, we declare a WHILE statement. A WHILE statement can be labelled (but labelling it is optional). Here we label it while_example...
REPEAT statement
Next, let’s look at the REPEAT statement. A REPEAT statement is also used to perform repetitive tasks.
It repeatedly performs some tasks until the UNTIL condition is met. The syntax of a REPEAT statement is:
[name of repeat statement :] REPEAT
-- do some tasks
UNTIL stop condition is met
END REPEAT;
Example
DELIMITER $$
CREATE FUNCTION repeat_demo(x INT, y INT) RETURNS VARCHAR(255) DETERMINISTIC
BEGIN
DECLARE z VARCHAR(255);
SET z = '';
REPEAT
SET x = x + 1;
SET z = concat(z, x);
UNTIL x>=y
END REPEAT;
RETURN z;
END $$
DELIMITER ;
This REPEAT statement repeats two tasks (SET x = x + 1 and SET z = concat(z, x)) until the x >= y condition is met.
If you run the function with the following statement
SELECT repeat_demo(1, 5);
You’...
LOOP statement
Last but not least, let’s move on to the LOOP statement. This statement is very similar to the WHILE and REPEAT statements, except that it does not come with a condition to exit the loop.
Instead, we use the ITERATE or LEAVE keywords to exit it.
The syntax of a LOOP statement is:
[name of loop statement :] LOOP
-- some tasks
END LOOP;
Let’s look at a few examples.
Example 1
DELIMITER $$
CREATE FUNCTION loop_demo_A(x INT, y INT) RETURNS VARCHAR(255) DETERMINISTIC
BEGIN
DECLARE z VARCHAR(255);
SET z = '';
simple_loop: LOOP
SET x = x + 1;
IF x > y THEN
LEAVE simple_loop;
END IF;
SET z = concat(z,...
