mysql 多个游标_如何在MySQL中的单个存储过程中使用多个游标

在

manual它说

Cursor declarations must appear before handler declarations and after variable and condition declarations.

此外,你不能有多个继续处理程序(MySQL应该如何知道哪个继续处理程序与哪个游标相关？不幸的是你不能指定它),除非你嵌套它们,例如：

DELIMITER $$

CREATE PROCEDURE `spTest`(OUT v1 VARCHAR(500), OUT v2 VARCHAR(500))

BEGIN

BLOCK1:BEGIN

DECLARE variable1 INT;

DECLARE _cur_1 CURSOR FOR SELECT id FROM tbl_1;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET _cur1Done = 1;

LOOP1: LOOP

FETCH _cur_1 INTO variable1;

IF _cur1Done THEN

CLOSE _cur_1;

LEAVE LOOP1;

END IF;

BLOCK2:BEGIN

DECLARE variable2 INT;

DECLARE _cur_2 CURSOR FOR SELECT id FROM tbl_2;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET _cur2Done = 1;

OPEN _cur_2;

LOOP2: LOOP

FETCH _cur_2 INTO variable2;

IF _cur2Done THEN

CLOSE _cur_2;

LEAVE LOOP2;

END IF;

END LOOP LOOP2;

END BLOCK2;

END LOOP LOOP1;

END BLOCK1;

END $$

DELIMITER ;