MySQL MySQL中嵌套循环中的多个游标
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6099500/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Multiple cursors in nested loops in MySQL
提问by user5537
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.
我想做一些在 MySQL 中看起来有点复杂的事情。事实上，我希望打开一个游标，做一个循环，在这个循环中，使用要执行的前一次提取的数据打开第二个游标，并对结果重新循环。
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.
事实上，如何对两个游标执行不同的“直到完成”，因为您只能为 SQLSTATE 声明一个处理程序？如果第一个结束，第二个也结束。
回答by Pentium10
You need to define a new BLOCK inside your 1st cursor loop and use different Declares in that block.
您需要在第一个游标循环中定义一个新的 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;
回答by DRapp
Correct me if I'm wrong, but it looks like what you are trying to do is a bulk insert of records into your "SLA_DEMANDE_STATUS" table. Include every criteria for every indicator found and default it with the values of '0009', 'OK' and 10.0 per each indicator's Criteria ID.
如果我错了，请纠正我，但看起来您要做的是将记录批量插入到“SLA_DEMANDE_STATUS”表中。包括找到的每个指标的每个标准，并将其默认为每个指标的标准 ID 的值“0009”、“OK”和 10.0。
This can all be done in a single SQL-Insert. INSERT INTO ... from a SQL-Select...
这一切都可以在单个 SQL-Insert 中完成。INSERT INTO ... 从 SQL-Select ...
Now, if you want to only include a single "id_indicateur" entry, you can add that to the WHERE clause of the select statement.
现在，如果您只想包含一个“id_indicateur”条目，您可以将其添加到 select 语句的 WHERE 子句中。
Notice that my SQL-Select has forced values to correspond with the columns you want to have populated. They will all be inserted to the destination table by the same name. The nice thing about this, you can just run the SQL-SELECT portion just to see the data you would EXPECT to have inserted... if its incorrect, you can adjust it to fit whatever restrictions you want.
请注意，我的 SQL-Select 已强制值与您要填充的列相对应。它们都将以相同的名称插入到目标表中。这样做的好处是，您只需运行 SQL-SELECT 部分即可查看您希望插入的数据……如果不正确，您可以调整它以适应您想要的任何限制。
insert into SLA_DEMANDE_STATUS
( iddemande,
idindicateur,
indicateur_status,
progression )
SELECT
'0009' iddemande,
c.id_criterere idindicateur,
'OK' indicateur_status,
10.0 progression
FROM
indicateur i;
JOIN critere c
ON i.id_indicateur = c.id_indicateur
回答by hyphen
you could use loop,and reset the value of the handle,like this:
您可以使用循环，并重置句柄的值，如下所示：
get_something:loop
open cur;
fetch cur into temp_key;
if no_more_record=1 then
set no_more_record=0;
close cur;
leave get_something;
else
//do your job;
end if;
end loop;
回答by B.F.
Or redefine the CONTINUE HANDLE:
或者重新定义 CONTINUE 句柄：
//...
LOOP1: LOOP
fetch cursor1
into v_col1;
if no_more_rows1 then
close cursor1;
leave LOOP1;
end if;
//...
SET no_more_rows1=false;//That's new
END LOOP LOOP1;
It seems that all select statements inside a loop execute the CONTINUE HANDLE
似乎循环内的所有选择语句都执行 CONTINUE HANDLE
回答by Tommy Leonard
DECLARE _idp INT;
DECLARE _cant INT;
DECLARE _rec INT;
DECLARE done INT DEFAULT 0;
-- Definición de la consulta
DECLARE primera CURSOR FOR SELECT dp.id_prod, SUM(dp.cantidad) AS cantidad, pp.receta FROM tm_detalle_pedido AS dp INNER JOIN tm_producto_pres AS pp
DECLARE segunda CURSOR FOR SELECT id_ins, cant FROM tm_producto_ingr WHERE id_pres = _idp;
-- Declaración de un manejador de error tipo NOT FOUND
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
-- Abrimos el primer cursor
OPEN primera;
REPEAT
FETCH primera INTO _idp, _cant, _rec;
IF NOT done THEN
OPEN segunda;
block2: BEGIN
DECLARE doneLangLat INT DEFAULT 0;
DECLARE _ii INT;
DECLARE i FLOAT;
DECLARE _canti FLOAT;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET doneLangLat = 1;
REPEAT
FETCH segunda INTO _ii,_canti;
IF NOT doneLangLat THEN
IF _rec = 1 THEN
SET i = _canti * _cant;
-- Insertamos
INSERT INTO tm_inventario (id_ins,id_tipo_ope,id_cv,cant,fecha_r)
VALUES (_ii, 2, @id, i, _fecha);
END IF;
END IF;
UNTIL doneLangLat END REPEAT;
END block2;
CLOSE segunda;
END IF;
UNTIL done END REPEAT;
CLOSE primera;
