Mysql 存储过程中使用多游标

drop procedure IF EXISTS test_proc_1;

create procedure test_proc_1()

begin

    DECLARE done INT DEFAULT 0;

    DECLARE tid int(11) DEFAULT 0;

    DECLARE tname varchar(50) DEFAULT NULL;

    DECLARE tpass varchar(50) DEFAULT NULL;

    DECLARE cur_1 CURSOR FOR

        select name, password from netingcn_proc_test;

    DECLARE cur_2 CURSOR FOR

        select id, name from netingcn_proc_test;

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

    open cur_1;

    REPEAT

        FETCH cur_1 INTO tname, tpass;

        if not done then

            select tname, tpass;

        end if;

     UNTIL done END REPEAT;

    CLOSE cur_1;

    -- 注意这里，一定要重置done的值为 0

    set done = 0;

    open cur_2;

    REPEAT

        FETCH cur_2 INTO tid, tname;

        if not done then

            select tid, tname;

        end if;

     UNTIL done END REPEAT;

    CLOSE cur_2;

end

call test_proc_1();

或者

drop procedure IF EXISTS test_proc_2;

create procedure test_proc_2()

begin

    DECLARE done INT DEFAULT 0;

    DECLARE tname varchar(50) DEFAULT NULL;

    DECLARE tpass varchar(50) DEFAULT NULL;

    DECLARE cur_1 CURSOR FOR

        select name, password from netingcn_proc_test;

    DECLARE cur_2 CURSOR FOR

        select id, name from netingcn_proc_test;

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

    open cur_1;

    REPEAT

        FETCH cur_1 INTO tname, tpass;

        if not done then

            select tname, tpass;

        end if;

     UNTIL done END REPEAT;

    CLOSE cur_1;

    begin

        DECLARE done INT DEFAULT 0;

        DECLARE tid int(11) DEFAULT 0;

        DECLARE tname varchar(50) DEFAULT NULL;

        DECLARE cur_2 CURSOR FOR

            select id, name from netingcn_proc_test;

        DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

        open cur_2;

        REPEAT

            FETCH cur_2 INTO tid, tname;

            if not done then

                select tid, tname;

            end if;

         UNTIL done END REPEAT;

        CLOSE cur_2;

    end;

end

call test_proc_2();