oracle 使用 Cursor 变量在存储过程中插入循环
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19434372/
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
Use Cursor variable to insert with a loop in a store procedure
提问by user2084446
I have a store procedure and I need to take all id′s from a table and insert new rows in other table with those id′s but i dont′t understand well the function cursor
我有一个存储过程，我需要从一个表中取出所有 id 并使用这些 id 在其他表中插入新行，但我不太了解函数游标
PROCEDURE INSERTMDCGENERAL AS
idCat NUMERIC;
CURSOR cur IS
SELECT ID_CAT_FILTROS_TALENTO into idCat FROM MDC_CAT_FILTROS_TALENTO;
BEGIN
FOR v_reg IN cur LOOP
INSERT INTO MDC_FILTROS_TALENTO(ID_FILTRO,ID_CAT_FILTROS_TALENTO)
VALUES(SEC_MDC_FILTROS_TALENTO.NextVal,idCat);
END LOOP;
COMMIT;
END INSERTMDCGENERAL;
回答by APC
There is rarely any point in doing anything more complex than:
做比以下更复杂的事情几乎没有任何意义：
PROCEDURE INSERTMDCGENERAL AS
BEGIN
INSERT INTO MDC_FILTROS_TALENTO
(ID_FILTRO,ID_CAT_FILTROS_TALENTO)
SELECT SEC_MDC_FILTROS_TALENTO.NextVal
, ID_CAT_FILTROS_TALENTO
FROM MDC_CAT_FILTROS_TALENTO;
COMMIT;
END INSERTMDCGENERAL;
This should work in most cases. Only if you are dealing with millions of rows is it likely that you will need to embellish this. Even then you should not use a cursor loop and Row-By-Row processing: that is vastly more inefficient.
这应该在大多数情况下工作。只有当您处理数百万行时，您才可能需要修饰它。即便如此，您也不应该使用游标循环和逐行处理：那样效率低得多。
回答by akzhere
This might be what you are expecting...
这可能是你所期待的......
PROCEDURE INSERTMDCGENERAL AS
idCat NUMERIC;
CURSOR cur IS SELECT ID_CAT_FILTROS_TALENTO FROM MDC_CAT_FILTROS_TALENTO;
BEGIN
open cur;
loop
fetch cur into idCat;
exit when cur%notfound;
INSERT INTO MDC_FILTROS_TALENTO(ID_FILTRO,ID_CAT_FILTROS_TALENTO)
VALUES(SEC_MDC_FILTROS_TALENTO.NextVal,idCat);
END LOOP;
close cur;
COMMIT;
END INSERTMDCGENERAL;
