MySQL 5.0 新特性教程 存储过程:第三讲

ITERATE: Walking through the loop

深入循环

CREATE PROCEDURE p20 ()

BEGIN

DECLARE v INT;

SET v = 0;

loop_label: LOOP <--

IF v = 3 THEN

SET v = v + 1;

ITERATE loop_label;

END IF;

INSERT INTO t VALUES (v);

SET v = v + 1;

IF v >= 5 THEN

LEAVE loop_label;

END IF;

END LOOP;

END; //

　　让这个已经定义了标号的循环运行起来。

ITERATE: Walking through the loop

CREATE PROCEDURE p20 ()

BEGIN

DECLARE v INT;

SET v = 0;

loop_label: LOOP

IF v = 3 THEN <--

SET v = v + 1;

ITERATE loop_label;

END IF;

INSERT INTO t VALUES (v);

SET v = v + 1;

IF v >= 5 THEN

LEAVE loop_label;

END IF;

END LOOP;

END; //

v的值变成3，然后我们把它增加到4。

ITERATE: walking through the loop

CREATE PROCEDURE p20 ()

BEGIN

DECLARE v INT;

SET v = 0;

loop_label: LOOP

IF v = 3 THEN

SET v = v + 1;

ITERATE loop_label; <--

END IF;

INSERT INTO t VALUES (v);

SET v = v + 1;

IF v >= 5 THEN

LEAVE loop_label;

END IF;

END LOOP;

END; //

　　然后开始ITERATE（迭代）过程。

ITERATE: walking through the loop

CREATE PROCEDURE p20 ()

BEGIN

DECLARE v INT;

SET v = 0;

loop_label: LOOP <--

IF v = 3 THEN

SET v = v + 1;

ITERATE loop_label;

END IF;

INSERT INTO t VALUES (v);

SET v = v + 1;

IF v >= 5 THEN

LEAVE loop_label;

END IF;

END LOOP;

END; //

这里的ITERATE（迭代）让循环又回到了循环的头部。

ITERATE: walking through the loop

CREATE PROCEDURE p20 ()

BEGIN

DECLARE v INT;

SET v = 0;

loop_label: LOOP

IF v = 3 THEN

SET v = v + 1;

ITERATE loop_label;

END IF;

INSERT INTO t VALUES (v);

SET v = v + 1;

IF v >= 5 THEN

LEAVE loop_label; <--

END IF;

END LOOP;

END; //

　　当v的值变为5时，程序将执行LEAVE语句

ITERATE: walking through the loop

CREATE PROCEDURE p20 ()

BEGIN

DECLARE v INT;

SET v = 0;

loop_label: LOOP

IF v = 3 THEN

SET v = v + 1;

ITERATE loop_label;

END IF;

INSERT INTO t VALUES (v);

SET v = v + 1;

IF v >= 5 THEN

LEAVE loop_label;

END IF;

END LOOP;

END; // <--

LEAVE的结果就是跳出循环，使运行指令到达复合语句的最后一步。

GOTO

CREATE PROCEDURE p...

BEGIN

...

LABEL label_name;

...

GOTO label_name;

...

END;

　　MySQL的存储过程中可以使用GOTO语句。虽然这不是标准SQL语句，而且在这里建立标号的方法也和惯例中的不一样。由于为了和其他DBMS兼容，这个语句会慢被淘汰，所以我们在MySQL参考手册中没有提及。

Grand combination

大组合

CREATE PROCEDURE p21

(IN parameter_1 INT, OUT parameter_2 INT)

LANGUAGE SQL DETERMINISTIC SQL SECURITY INVOKER

BEGIN

DECLARE v INT;

label goto_label; start_label: LOOP

IF v = v THEN LEAVE start_label;

ELSE ITERATE start_label;

END IF;

END LOOP start_label;

REPEAT

WHILE 1 = 0 DO BEGIN END;

END WHILE;

UNTIL v = v END REPEAT;

GOTO goto_label;

END;//