MySQL中嵌套循环中的多个游标 [英] Multiple cursors in nested loops in MySQL

问题描述

我想做一些在MySQL中显得有点复杂的东西。
事实上，我想打开一个游标，做一个循环，在这个循环中，打开一个第二个游标，使用来自上一次抓取的数据执行，并重新循环结果。

I wish to do something which appear a bit complicated in MySQL. In fact, I wish to open a cursor, do a loop, and in this loop, open a second cursor using the data from the previous fetch to be executed, and re-loop on the results.

  DECLARE idind INT;
  DECLARE idcrit INT;
  DECLARE idindid INT;
  DECLARE done INT DEFAULT 0;
  DECLARE done2 INT DEFAULT 0;
  DECLARE curIndicateur CURSOR FOR SELECT id_indicateur FROM indicateur;
  DECLARE curCritereIndicateur CURSOR FOR SELECT C.id_critere FROM critere C where C.id_indicateur=idind;
  DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;

  set idindid=54;
  OPEN curIndicateur;
  REPEAT
    FETCH curIndicateur INTO idind;
    open curCritereIndicateur;
    REPEAT
      FETCH curIndicateur INTO idcrit;
      INSERT INTO SLA_DEMANDE_STATUS (iddemande,idindicateur,indicateur_status,progression) values('0009',idcrit,'OK',10.0);
    UNTIL done END REPEAT;
    close curCritereIndicateur;
  UNTIL done END REPEAT;
  CLOSE curIndicateur;

事实上，如何做'Until done'不同于两个游标，因为你只能声明一个处理程序为SQLSTATE？

In fact, how to do 'Until done' differently for the two cursors, because you can only declare one handler for SQLSTATE? If the first ends, the second ends too.

推荐答案

您需要在第一个光标内定义一个新的BLOCK循环，并在该块中使用不同的声明。

You need to define a new BLOCK inside your 1st cursor loop and use different Declares in that block.

类似：

BLOCK1: begin
    declare v_col1 int;                     
    declare no_more_rows boolean1 := FALSE;  
    declare cursor1 cursor for              
        select col1
        from   MyTable;
    declare continue handler for not found  
        set no_more_rows1 := TRUE;           
    open cursor1;
    LOOP1: loop
        fetch cursor1
        into  v_col1;
        if no_more_rows1 then
            close cursor1;
            leave LOOP1;
        end if;
        BLOCK2: begin
            declare v_col2 int;
            declare no_more_rows2 boolean := FALSE;
            declare cursor2 cursor for
                select col2
                from   MyOtherTable
                where  ref_id = v_col1;
           declare continue handler for not found
               set no_more_rows2 := TRUE;
            open cursor2;
            LOOP2: loop
                fetch cursor2
                into  v_col2;
                if no_more_rows then
                    close cursor2;
                    leave LOOP2;
                end if;
            end loop LOOP2;
        end BLOCK2;
    end loop LOOP1;
end BLOCK1;

这篇关于MySQL中嵌套循环中的多个游标的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！