gpt4 book ai didi

oracle - Oracle 触发器中的行编号如何工作?

转载 作者:行者123 更新时间:2023-12-02 00:03:13 24 4
gpt4 key购买 nike

我有一个引发错误的触发器,但我不确定如何知道哪一行 PL/SQL 代码引发了该错误。我的错误是

[Oracle]ORA-01403: no data found ORA-06512: at "MYSCHEMA.FOO_BI", line 9

我的触发器是这样的:

create or replace TRIGGER "MYSCHEMA"."FOO_BI" 
BEFORE INSERT ON FOO REFERENCING OLD AS OLD NEW AS NEW FOR EACH ROW
DECLARE
NUM1 NUMBER;
NUM2 NUMBER;

BEGIN

-- some comment
if :new.batch_num is null then
SELECT COUNT(*) INTO :NEW.BATCH_NUM FROM FOO WHERE CORP_ID = :NEW.CORP_ID;
end if;

if :new.batch_id is null or :new.batch_id = '' then
:NEW.BATCH_ID := :NEW.CORP_ID || '-' || :NEW.BATCH_NUM;
end if;

/* etc... */

我发现了类似 similar question 的内容但行编号以创建或替换...开头,并将我的错误行表示为注释,我认为这一定是假的。触发器执行中抛出错误时如何报告行号?

最佳答案

行编号(如堆栈跟踪中所报告)从第 1 行的 DECLARE 开始。因此,如果执行以下操作:

CREATE OR REPLACE TRIGGER foo
BEFORE INSERT ON test1
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW
DECLARE
n1 NUMBER := 1;
n2 NUMBER := 2;

BEGIN

-- a comment
IF :new.n1 IS NULL THEN
n1 := n2/0;
END IF;
END;
/

SQL> insert into test1 values (3,'XX','YY',NULL);

insert into test1 values (3,'XX','YY',NULL)

ORA-01476: divisor is equal to zero
ORA-06512: at "XXX.FOO", line 9
ORA-04088: error during execution of trigger 'XXX.FOO'

SQL> select line, text from all_source where name = 'FOO';

LINE TEXT
---------- --------------------------------------------------------------------------------
1 TRIGGER foo
2 BEFORE INSERT ON test1
3 REFERENCING OLD AS OLD NEW AS NEW
4 FOR EACH ROW
5 DECLARE
6 n1 NUMBER := 1;
7 n2 NUMBER := 2;
8
9 BEGIN
10
11 -- a comment
12 IF :new.n1 IS NULL THEN
13 n1 := n2/0;
14 END IF;
15 END;

15 rows selected

您可以看到错误报告为发生在第 9 行,实际上是源代码中的第 13 行。

关于oracle - Oracle 触发器中的行编号如何工作?,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/2999482/

24 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com