mysql 的三种循环操作 while do ... end while 循环 loop ... end loop 循环 repeat ... end repeat 循环
/joyous/article/details/80169435
while do ... end while 循环
语法:
[begin_label:] WHILE search_condition DOstatement_listEND WHILE [end_label]
举例:
CREATE PROCEDURE dowhile()BEGINDECLARE v1 INT DEFAULT 5;WHILE v1 > 0 DO...SET v1 = v1 - 1;END WHILE;END;
while .. end while 的循环条件在循环体开始之前,满足条件则进入循环体。
repeat ... end repeat 循环
语法:
[begin_label:] REPEATstatement_listUNTIL search_conditionEND 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)
repeat ... end repeat 循环和 while ... end while 循环有些类似,但是条件判断在循环体末尾,也就是先执行了循环体,再进行条件判断,满足条件了则再次进入循环体,直到条件不成立以后跳出循环。
loop ... end loop 循环
语法:
[begin_label:] LOOPstatement_listEND LOOP [end_label]
举例:
CREATE PROCEDURE doiterate(p1 INT)BEGINlabel1: LOOPSET p1 = p1 + 1;IF p1 < 10 THENITERATE label1;END IF;LEAVE label1;END LOOP label1;SET @x = p1;END;
loop ... leave ... end loop 有点类似饱受争议争议的 goto 命令,但 loop 循环具有严格的循环体,而 goto 循环则是强制跳转,但 loop 循环也可以用于块的条件判断,在完成循环的同时,也适合做一些模块控制的功能,以优化代码逻辑。
/joyous/article/details/80169435
Q群讨论23601
相关资料:
/doc/refman/5.7/en/while.html
/doc/refman/5.7/en/loop.html
/doc/refman/5.7/en/repeat.html