MySQL Flow Control Constructs

1. IF Statement

IF search_condition THEN statement_list
 [ELSEIF search_condition THEN statement_list] ...
 [ELSE statement_list]
END IF

IF()	If/else construct
IFNULL()	Null if/else construct
NULLIF()	Return NULL if expr1 = expr2

IF(expr1,expr2,expr3)

If expr1 is TRUE (expr1 <> 0 and expr1 <> NULL) then IF() returns expr2; otherwise it returns expr3. IF() returns a numeric or string value, depending on the context in which it is used.

EX
mysql> SELECT IF(1>2,2,3);
     -> 3
mysql> SELECT IF(1<2,'yes','no');
     -> 'yes'
mysql> SELECT IF(STRCMP('test','test1'),'no','yes');
     -> 'no'

If only one of expr2 or expr3 is explicitly NULL, the result type of the IF() function is the type of the non-NULL expression.

expr1 is evaluated as an integer value, which means that if you are testing floating-point or string values, you should do so using a comparison operation.

EX
mysql> SELECT IF(0.1,1,0);
        -> 0
mysql> SELECT IF(0.1<>0,1,0);
       -> 1

In the first case shown, IF(0.1) returns 0 because 0.1 is converted to an integer value, resulting in a test of IF(0). This may not be what you expect. In the second case, the comparison tests the original floating-point value to see whether it is non-zero. The result of the comparison is used as an integer.

The default return type of IF() (which may matter when it is stored into a temporary table) is calculated as follows:

2. CASE Statement

CASE case_value
 WHEN when_value THEN statement_list
 [WHEN when_value THEN statement_list] ...
 [ELSE statement_list]
END CASE

Or:

CASE
 WHEN search_condition THEN statement_list
 [WHEN search_condition THEN statement_list] ...
 [ELSE statement_list]
END CASE

The CASE statement for stored routines implements a complex conditional construct. If a search_condition evaluates to true, the corresponding SQL statement list is executed. If no search condition matches, the statement list in the ELSE clause is executed. Each statement_list consists of one or more statements.

Note

The syntax of the CASE statement shown here for use inside stored routines differs slightly from that of the SQL CASE expression described in “Control Flow Functions”. The CASE statement cannot have an ELSE NULL clause, and it is terminated with END CASE instead of END.

EX
mysql> SELECT CASE 1 WHEN 1 THEN 'one'
    ->     WHEN 2 THEN 'two' ELSE 'more' END;
        -> 'one'
mysql> SELECT CASE WHEN 1>0 THEN 'true' ELSE 'false' END;
        -> 'true'
mysql> SELECT CASE BINARY 'B'
    ->     WHEN 'a' THEN 1 WHEN 'b' THEN 2 END;
        -> NULL

3. LOOP Statement

[begin_label:] LOOP
    statement_list
END LOOP [end_label]

LOOP implements a simple loop construct, enabling repeated execution of the statement list, which consists of one or more statements. The statements within the loop are repeated until the loop is exited; usually this is accomplished with a LEAVE statement.

4. LEAVE Statement

LEAVE label

This statement is used to exit any labeled flow control construct. It can be used within BEGIN ... END or loop constructs (LOOP, REPEAT, WHILE).

5. ITERATE Statement

ITERATE label     

ITERATE can appear only within LOOP, REPEAT, and WHILE statements. ITERATE means “do the loop again.”

EX
CREATE PROCEDURE doiterate(p1 INT)
BEGIN
  label1: LOOP
    SET p1 = p1 + 1;
    IF p1 <>
    LEAVE label1;
  END LOOP label1;
  SET @x = p1;
END

6. REPEAT Statement

[begin_label:] REPEAT statement_list UNTIL search_condition END REPEAT [end_label]

The statement list within a REPEAT statement is repeated until the search_condition is true. Thus, a REPEAT always enters the loop at least once. statement_list consists of one or more statements.

A REPEAT statement can be labeled. end_label cannot be given unless begin_label also is present. If both are present, they must be the same.

EX
mysql> delimiter //
mysql> CREATE PROCEDURE dorepeat(p1 INT)
 -> BEGIN
 ->   SET @x = 0;
 ->   REPEAT SET @x = @x + 1; UNTIL @x > p1 END REPEAT;
 -> END
 -> //
Query OK, 0 rows affected (0.00 sec)

mysql> CALL dorepeat(1000)//
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @x//
+------+
| @x   |
+------+
| 1001 |
+------+
1 row in set (0.00 sec)

7. WHILE Statement

[begin_label:] WHILE search_condition DO
 statement_list
END WHILE [end_label]

The statement list within a WHILE statement is repeated as long as the search_condition is true. statement_list consists of one or more statements.

A WHILE statement can be labeled. end_label cannot be given unless begin_label also is present. If both are present, they must be the same.

EX
CREATE PROCEDURE dowhile()
BEGIN
DECLARE v1 INT DEFAULT 5;

WHILE v1 > 0 DO
 ...
 SET v1 = v1 - 1;
END WHILE;
END