mysql_存储过程-循环
(1)WHILE … END WHILE 举例:
CREATE PROCEDURE p4 ()
BEGIN
DECLARE v INT;
SET v = 0;
WHILE v < 5 DO
INSERT INTO t VALUES (v);
SET v = v + 1;
END WHILE;
END //
(2)LOOP … END LOOP 举例:
CREATE PROCEDURE p5 ()
BEGIN
DECLARE v INT;
SET v = 0;
loop_label: LOOP
INSERT INTO t VALUES (v);
SET v = v + 1;
IF v >= 5 THEN
LEAVE loop_label;
END IF;
END LOOP;
END //
[begin_label:] LOOP
statement_list
END LOOP [end_label]
LOOP允许某特定语句或语句群的重复执行,实现一个简单的循环构造。在循环内的语句一直重复直到循环被退出,退出通常伴随着一个LEAVE 语句。
例子:
DROP PROCEDURE IF EXISTS prg_cursor;
DELIMITER $$
CREATE PROCEDURE prg_cursor()
BEGIN
DECLARE i int DEFAULT 1;
loop_name:loop
if i <=a THEN
set i=i+1;
ITERATE loop_name;/*ITERATE则继续循环,好比编程里面的continue一样*/
end IF;
if i>a then
SET i=i+1;
leave loop_name;/*而LEAVE表示离开循环,好比编程里面的break一样*/
end if;
set i=i+1;
end loop;
select i;
END$$
CALL prg_cursor()
(3)repeat循环:
REPEAT Statements;
UNTIL expression
END REPEAT
DELIMITER $$
DROP PROCEDURE IF EXISTS RepeatLoopProc
$$
CREATE PROCEDURE RepeatLoopProc()
BEGIN
DECLARE x INT;
DECLARE str VARCHAR(255);
SET x = 1; SET str = '';
REPEAT
SET str = CONCAT(str,x,',');
SET x = x + 1;
UNTIL x > 5 END REPEAT;
SELECT str; END
$$
DELIMITER ;
在REPEAT语句中不管是否满足给定条件,首先会执行一次statements, 然后再在UTILE中判断给定的条件是否成立,如果条件不成立会继续执行,
如果条件成立则退出REPEAT循环