mysql - 在游标循环中调用存储过程，而不触发继续处理程序

Question

我试图在游标循环中调用 MySQL 中的存储过程。在循环中执行 INSERT 时，游标行为正常；但是如果我尝试调用存储过程，则在处理完第一条记录后，continue Handler 'sets done = 1' 将过早退出循环。关于如何解决这个问题的任何想法？谢谢。

declare test_cursor cursor for 
       select projectid, projectdesc
         from tblProjects
        order by projectdesc;          

DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;

set done = 0;  
open test_cursor;

repeat

  fetch test_cursor into wprojectid, wprojectdesc;

  if not done then  

    insert into tblTest (a, b) values (wprojectid, wprojectdesc);   <--this would work
    call spTest(wprojectid, wprojectdesc, @retrn);                                    <--this trips the Handler after first loop

  end if;      

until done end repeat;

close test_cursor; 

Answer 1

我不确定，但试试看这段代码是否有效？

declare test_cursor cursor for 
       select projectid, projectdesc
         from tblProjects
        order by projectdesc;          

DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
DECLARE done_holder INT;


set done = 0;
open test_cursor;

repeat

  fetch trade_cursor into wprojectid, wprojectdesc;

  if not done then  

    set done_holder = done;
    insert into tblTest (a, b) values wprojectid, wprojectdesc;
    call spTest(a, b, @retrn);
    set done = done_holder;

  end if;      

until done end repeat;

close test_cursor;