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;

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.

解决方案

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

Something like:

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;