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
Neglecting to include a loop-termination statement results in an infinite loop.
[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]
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]
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)
implements 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 astatement. Within a stored function,can also be used, which exits the function entirely.
Astatement can be labeled.
The statement list within astatement 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.
Astatement can be labeled.
The statement list within astatement is repeated until thesearch_condition_expression is true. Thus, aalways enters the loop at least once.statement_list_consists of one or more statements, each terminated by a semicolon (;) statement delimiter.
Astatement can be labeled.
Thestatement terminates execution of a stored function and returns the value_expr_to the function caller. There must be at least onestatement 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. Thestatement can be used to exit a stored program of those types.
can appear only within,, andstatements.means “start the loop again.”
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,exits the program.