How to use many cursors in a single store procedure in MySQL

Question

I want to create a store procedure that has ability to do multi-tasks. Then it got error message below

Error Code: 1338 Cursor declaration after handler declaration

Please look my store procedure

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

    DECLARE  _cur_1 CURSOR FOR
        SELECT id
        FROM tbl_1;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET _cur1Done = 1;

    DECLARE  _cur_2 CURSOR FOR
        SELECT id
        FROM tbl_2;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET _cur2Done = 1;

    .......

Answer 1

You may only have one continue handler active in a block. Multiple continue handler's get the Duplicate handler declared in same block issue, even if you get the order of declarations correct ( all cursors first, then the handlers later).

You can work around this by creating a block devoted to a cursor's access. You can place these chunks of code in the same parent block, or in nested loops, or wherever.

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

    declare tbl_1_id int;
    declare tbl_2_id int;

    declare _cur1Done boolean default false;
    declare _cur2Done boolean default false;

    DECLARE  _cur_1 CURSOR FOR SELECT id FROM tbl_1;
    DECLARE  _cur_2 CURSOR FOR SELECT id FROM tbl_2;

    begin -- dedicated block to fetch from cursor 1 and update its flag
        declare continue handler for not found set _cur1Done = TRUE;
        fetch _cur_1 into tbl_1_id;
    end;

    begin -- dedicated block to fetch from cursor 2 and update its flag
        declare continue handler for not found set _cur2Done = TRUE;
        fetch _cur_2 into tbl_2_id;
    end;
END $$
DELIMITER ;

Answer 2

In the manual it says

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

Also you can not have multiple continue handlers (how should MySQL know which continue handler is related to which cursor? Unfortunately you can't specify that), unless you nest them, for example like this:

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 ;