Flow Control Statements
MySQL supports the IF, CASE, ITERATE, LEAVE LOOP, WHILE, and REPEAT constructs for flow control within stored programs. It also supports RETURN within stored functions.
Many of these constructs contain other statements, as indicated by the grammar specifications in the following sections. Such constructs may be nested. For example, an IF statement might contain a WHILE loop, which itself contains a CASE statement.
MySQL does not support FOR loops.
CASE Syntax
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
Example:
DELIMITER |
CREATE PROCEDURE p()
BEGIN
DECLARE v INT DEFAULT 1;
CASE v
WHEN 2 THEN SELECT v;
WHEN 3 THEN SELECT 0;
ELSE
BEGIN
END;
END CASE;
END;
|
IF Syntax
IF search_condition THEN statement_list
[ELSEIF search_condition THEN statement_list] …
[ELSE statement_list]
END IF
Example:
DELIMITER //
CREATE FUNCTION SimpleCompare(n INT, m INT)
RETURNS VARCHAR(20)
BEGIN
DECLARE s VARCHAR(20);
IF n > m THEN SET s = ‘>’;
ELSEIF n = m THEN SET s = ‘=’;
ELSE SET s = ‘<’;
END IF;
SET s = CONCAT(n, ‘ ‘, s, ‘ ‘, m);
RETURN s;
END //
DELIMITER ;
DELIMITER //
CREATE FUNCTION VerboseCompare (n INT, m INT)
RETURNS VARCHAR(50)
BEGIN
DECLARE s VARCHAR(50);
IF n = m THEN SET s = ‘equals’;
ELSE
IF n > m THEN SET s = ‘greater’;
ELSE SET s = ‘less’;
END IF;
SET s = CONCAT(‘is ‘, s, ‘ than’);
END IF;
SET s = CONCAT(n, ‘ ‘, s, ‘ ‘, m, ‘.’);
RETURN s;
END //
DELIMITER ;
LOOP Syntax
[begin_label:] LOOP
statement_list
END LOOP [end_label]
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;
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)
RETURN Syntax
RETURN expr
The RETURN statement terminates execution of a stored function and returns the value expr to the function caller. There must be at least one RETURN statement 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. The LEAVE statement can be used to exit a stored program of those types.
WHILE Syntax
[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 _searchcondition expression is true. _statementlistconsists of one or more SQL statements, each terminated by a semicolon (;) statement delimiter.
A WHILE statement can be labeled. For the rules regarding label use, see Section 13.6.2, “Statement Label Syntax”.
Example:
CREATE PROCEDURE dowhile()
BEGIN
DECLARE v1 INT DEFAULT 5;
WHILE v1 > 0 DO
…
SET v1 = v1 - 1;
END WHILE;
END;
LEAVE Syntax
LEAVE label
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, LEAVE exits the program.
LEAVE can be used within BEGIN … END or loop constructs (LOOP, REPEAT, WHILE).
For an example, see Section 13.6.5.5, “LOOP Syntax”.
ITERATE Syntax
ITERATE label
ITERATE can appear only within LOOP, REPEAT, and WHILE statements. ITERATE means “start the loop again.”
For an example, see Section 13.6.5.5, “LOOP Syntax”.
Statement Label Syntax
[begin_label:] BEGIN
[statement_list]
END [end_label]
[begin_label:] LOOP
statement_list
END LOOP [end_label]
[begin_label:] REPEAT
statement_list
UNTIL search_condition
END REPEAT [end_label]
[begin_label:] WHILE search_condition DO
statement_list
END WHILE [end_label]
Labels are permitted for BEGIN … END blocks and for the LOOP, REPEAT, and WHILE statements. Label use for those statements follows these rules:
- _beginlabelmust be followed by a colon.
- _beginlabelcan be given without _endlabel. If _endlabel is present, it must be the same as _beginlabel.
- _endlabelcannot be given without _beginlabel.
- Labels at the same nesting level must be distinct.
- Labels can be up to 16 characters long.
To refer to a label within the labeled construct, use an ITERATE or LEAVE statement. The following example uses those statements to continue iterating or terminate the loop: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;
END;
More info on mysql:
最后更新： 2018年02月02日 23:25
原始链接： http://tekin.yunnan.ws/数据库开发/201711/23-mysql-flow-control-statements.html
