Control Flow Statements

CASE syntax

When we need to react differently in different conditions, we use CASE syntax. Here are two typical usages.

If we only need to test equality:

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

If we want to test other type of conditions:

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

Here is a example for the second usage:

SELECT OrderID, Quantity,
    CASE
        WHEN Quantity > 30 THEN "The quantity is greater than 30"
        WHEN Quantity = 30 THEN "The quantity is 30"
        ELSE "The quantity is something else"
    END
FROM OrderDetails;

CASE WHEN is usually used in SELECT statement, but it's possible to be used in other statements. Here is a example:

# order the customers by City, if City is NULL, then order by Country
SELECT CustomerName, City, Country
FROM Customers
ORDER BY
(CASE
    WHEN City IS NULL THEN Country
    ELSE City
END);

IF Syntax

IF statement works similar to CASE WHEN. In practice, we use CASE WHEN more often.

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

LOOP Syntax

LOOPimplements a simple loop construct, enabling repeated execution of the statement list, which consists of one or more statements, each terminated by a semicolon (;) statement delimiter. The statements within the loop are repeated until the loop is terminated. Usually, this is accomplished with aLEAVEstatement. Within a stored function,RETURNcan also be used, which exits the function entirely.

Neglecting to include a loop-termination statement results in an infinite loop.

ALOOPstatement can be labeled.

[begin_label:] LOOP
    statement_list
END LOOP [end_label]

Example:

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

WHILE Syntax

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

The statement list within aWHILEstatement is repeated as long as thesearch_condition_expression is true.statement_list_consists of one or more SQL statements, each terminated by a semicolon (;) statement delimiter.

AWHILEstatement can be labeled.

Example:

CREATE PROCEDURE dowhile()
BEGIN
  DECLARE v1 INT DEFAULT 5;
  WHILE v1 > 0 DO
    ...
    SET v1 = v1 - 1;
  END WHILE;
END;

REPEAT Syntax

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

The statement list within aREPEATstatement is repeated until thesearch_condition_expression is true. Thus, aREPEATalways enters the loop at least once.statement_list_consists of one or more statements, each terminated by a semicolon (;) statement delimiter.

AREPEATstatement can be labeled.

Example:

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)

RETURN Syntax

RETURN expr

TheRETURNstatement terminates execution of a stored function and returns the value_expr_to the function caller. There must be at least oneRETURNstatement in a stored function. There may be more than one if the function has multiple exit points.

This statement is not used in stored procedures, triggers, or events. TheLEAVEstatement can be used to exit a stored program of those types.

ITERATE Syntax

ITERATEcan appear only withinLOOP,REPEAT, andWHILEstatements.ITERATEmeans “start the loop again.”

ITERATE label

LEAVE Syntax

This statement is used to exit the flow control construct that has the given label. If the label is for the outermost stored program block,LEAVEexits the program.

LEAVEcan be used withinBEGIN ... ENDor loop constructs (LOOP,REPEAT,WHILE).

LEAVE label