调用游标循环中的存储过程，而不跳过继续处理程序 [英] Calling a Stored Procedure Within a Cursor Loop, Without Tripping the Continue Handler

问题描述

我试图在游标的循环中调用MySQL中的存储过程。在循环中执行INSERT时，游标运行正常;但是如果我尝试调用存储过程，则继续处理器设置done = 1'，并且将在处理第一个记录之后提前退出循环。关于如何解决这个问题的任何想法？谢谢。

I am trying to call a Stored Procedure in MySQL within the loop of a Cursor. The cursor behaves properly when doing an INSERT in the loop; but if I try to call a Stored Procedure, the continue Handler 'sets done = 1' and will exit the loop prematurely, after the first record is processed. Any thoughts on how to work around this? Thanks.

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; 

推荐答案

我不确定，

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; 

这篇关于调用游标循环中的存储过程，而不跳过继续处理程序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！