oracle 如何将 PL/SQL 游标用于触发器的循环
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15425195/
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
How can I use PL/SQL Cursor for loop for a Trigger
提问by cHaNkX
Hi I'm a newbie to plsql this is the first time I use plsql
嗨，我是 plsql 的新手，这是我第一次使用 plsql
I created trigger using plsql. Here is the syntax that I use to create that trigger. but it giving an error as "[Err] ORA-24344: success with compilation error" I cant figure out where I went wrong. In this trigger I use a for loop with a cursor.
I think something wrong with that cursor Can anyone help me to figure out where I went wrong . I use Navicat to do this. I'm struggling with this for almost 5 days :( thanks in advance
我使用 plsql 创建了触发器。这是我用来创建该触发器的语法。但它给出了一个错误为“ [Err] ORA-24344: success with compilation error”我无法弄清楚我哪里出错了。在这个触发器中，我使用了一个带有游标的 for 循环。我认为那个光标有问题 谁能帮我找出我哪里出错了。我使用 Navicat 来做到这一点。我为此苦苦挣扎了将近 5 天:( 提前致谢
CREATE OR REPLACE TRIGGER "c"
AFTER INSERT ON "EMP_REPORT_TO"
REFERENCING OLD AS "OLD" NEW AS "NEW"
FOR EACH ROW
DECLARE
miclaim_supervisor_count number;
employee_company_code number;
employee_businessunit number;
cursor projMgrsCursor is select b.BU_MEMBER_ID
from BU_MEMBER b, EMP_SUB_DIV s
where s.EMP_NO = :NEW.EMP_NO
and s.SUB_DIVISION_CODE = '02' and s.DIV_CODE = '041'
and b.BU_ID IN (select BU_ID from BU_MEMBER where BU_MEMBER_ID = :NEW.EMP_NO);
BEGIN
delete from MICL_SUPERVISORS where EMP_NO = :NEW.EMP_NO and IS_OVVERRIDDEN = 0;
select count(*) into miclaim_supervisor_count from MICL_SUPERVISORS where EMP_NO = :NEW.EMP_NO and IS_OVVERRIDDEN = 1;
select COMPANY_CODE into employee_company_code from EMPLOYEE_MASTER where EMP_NO = :NEW.EMP_NO;
if (employee_company_code = 'SOFT')then
OPEN projMgrsCursor;
FOR projMgrsCursor IN projMgrs
LOOP
insert into MICL_SUPERVISORS VALUES ((:NEW.ID), (SELECT SYSDATE FROM DUAL), :NEW.ENTRYADDEDBY_EMP_NO, 3000, 0, projMgrEmpNo, NULL,:NEW.EMP_NO);
END LOOP;
close projMgrsCursor;
else
if(miclaim_supervisor_count IS NULL or miclaim_supervisor_count<1) then
insert into MICL_SUPERVISORS VALUES ((:NEW.ID), (SELECT SYSDATE `enter code here`FROM DUAL), :NEW.ENTRYADDEDBY_EMP_NO, 3000, 0, :NEW.SUP_EMP_NO, NULL,:NEW.EMP_NO);
end if;
end if;
END;
;
采纳答案by cHaNkX
Thanks for all ur answers and time :)
感谢您的所有回答和时间:)
well I figured out where I went wrong thanks to oracle sql developeractually something wrong with for loop
好吧，多亏了oracle sql 开发人员，我才知道我哪里 出错了 for 循环实际上出了点问题
here is code (corrected code for loop)
这是代码（更正的循环代码）
OPEN projMgrsCursor;
LOOP
FETCH projMgrsCursor INTO projMgrs;
EXIT WHEN projMgrsCursor%NOTFOUND;
insert into
MICL_SUPERVISORS VALUES ((:NEW.ID), (SELECT SYSDATE FROM DUAL), :NEW.ENTRYADDEDBY_EMP_NO, 3000, 0,projMgrs, NULL,:NEW.EMP_NO);
END LOOP;
CLOSE projMgrsCursor;
Hope that this will help to anyone like me :)
希望这对像我这样的人有所帮助:)
回答by David Aldridge
No need for a cursor at all -- why not just use a SQL statement to perform the insert?
根本不需要游标——为什么不使用 SQL 语句来执行插入？
