1.loop
语法:
[begin_label
:] LOOPstatement_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 withinLOOP
,REPEAT
, andWHILE
statements.ITERATE
means “start the loop again.” END IF; LEAVE label1;//LEAVE
can be used withinBEGIN ... END
or loop constructs (LOOP
,REPEAT
,WHILE
). END LOOP label1; SET @x = p1; END;
2.repeat
语法
[begin_label
:] REPEATstatement_list
UNTILsearch_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
:] WHILEsearch_condition
DOstatement_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
:] LOOPstatement_list
END LOOP [end_label
] [begin_label
:] REPEATstatement_list
UNTILsearch_condition
END REPEAT [end_label
] [begin_label
:] WHILEsearch_condition
DOstatement_list
END WHILE [end_label
]