1.loop
语法：
[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;// ITERATE can appear only within LOOP, REPEAT, and WHILE statements. ITERATE means “start the loop again.”
END IF;
LEAVE label1;// LEAVE can be used within BEGIN ... END or loop constructs (LOOP, REPEAT, WHILE).
END LOOP label1;
SET @x = p1;
END;
2.repeat
语法
[begin_label:] REPEAT
statement_list
UNTIL search_condition
END REPEAT [end_label]
例子：
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)
3.while
语法
[begin_label:] WHILE search_condition DO
statement_list
END WHILE [end_label]
例子：
CREATE PROCEDURE dowhile()
BEGIN
DECLARE v1 INT DEFAULT 5;
WHILE v1 > 0 DO
...
SET v1 = v1 - 1;
END WHILE;
END;
4.label标记语法
[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]