oracle嵌套过程,oracle – Pl / SQL嵌套过程异常处理

这是通过多级PL / SQL过程进行错误处理的最佳实践问题.我已经看了几个其他问题来帮助我,特别是

this one.

目前,我有一个程序1,它调用过程2,它调用过程3.我正在尝试执行足够的错误处理 – 但我想最终将确切的问题输出回应用程序层.我希望得到一些关于如何有效和清楚地做到这一点的想法.

我现在的解决方法如下,但对我来说似乎相当混乱,有很多变量声明.我是PL / SQL(以及一般的SQL)的新手,所以我很感激任何建议：

>处理多层程序时的良好错误处理技术.

>将错误消息提供回应用程序层(在我的程序中,由“out_overall_output”变量表示).

程序流程：UI – > Proc 1 – > Proc 2 – > Proc 3

程序1：--One input variable, one output.

in_id VARCHAR2;

out_overall_output VARCHAR2;

...

DECLARE

l_success BOOLEAN;

l_error_output VARCHAR2(100);

BEGIN

Proc2(id, l_success, l_error_output);

IF l_success = FALSE THEN

out_overall_output = l_error_output

END IF

EXCEPTION

WHEN OTHERS THEN

ROLLBACK;

out_overall_output:= 'Error calling Proc 2'

RETURN;

END;

--Normal flow continues if l_success is true...

程序2：-- One input variable, two output.

in_id

out_success

out_error_output

//other logic

DECLARE

l_success BOOLEAN;

l_error_output VARCHAR2(100)

BEGIN

Proc3(id, l_success, l_error_output)

IF l_success = FALSE THEN

out_error_output = l_error_output

END IF

EXCEPTION

WHEN OTHERS

out_error_output = 'Error calling Proc 3'

RETURN;

END;

程序3：--One input variable, two output.

in_id VARCHAR2;

out_success BOOLEAN;

out_error_message VARCHAR2;

...

BEGIN

DELETE

FROM table

WHERE id = in_id;

EXCEPTION

WHEN NO_DATA_FOUND THEN

out_success = FALSE;

out_error_message = 'Error - No data to delete'

WHEN OTHERS THEN

out_success = FALSE;

out_error_message = 'Error deleting data.'

END;

注意：过程调用的级别比这更深.我所展示的片段大大简化了.我的实际过程中的错误消息和变量名称更具描述性.

最佳答案 要显示应用程序级别“服务器发生了什么”的确切说明,您可以尝试以下操作.在程序中：create or replace procedure p1 is

...

exception

when then

-- re-raise error:

raise_application_error(-20001, 'Client with ID '|| ID || ' has no right to perform action "' || ACTION_NAME || '"', true);

end;

create or replace procedure p2 is

begin

p1;

exception

when then

-- re-raise error:

raise_application_error(-20002, 'Action "' || ACTION_NAME || '" is not completed', true);

end;

create or replace procedure p3 is

begin

p2;

exception

when then

-- re-raise error:

raise_application_error(-20003, 'Purchasing of "' || CAR_NAME || '" cancelled', true);

end;

在顶级程序中：create or replace procedure top_level_procedure is

begin

p1;

exception

when then

raise_application_error(-20004, dbms_utility.format_error_backtrace);

end;

在p1中出现异常后,您将看到如下内容：ORA-20003: Purchasing of "Cool red Ferrari" cancelled

ORA-20002: Action "car purchase" is not completed

ORA-20001: Client with ID 123 has no right to perform action "Spent all money of Bill Gates"

具有false值的过程raise_application_error的第三个参数会切断所有先前的错误消息.如果在过程p3中使用false值,则在此示例中只会看到一条带有代码ORA-20003的错误消息.

P. P. S.如何记录.记录程序：create or replace procedure log(p_log_message varchar2) is

pragma autonomous_transaction;

begin

insert into log_table(..., log_message) values (..., p_log_message);

commit;

end;

通话记录程序：when then

log(..., dbms_utility.format_error_backtrace);

raise_application_error(-20004, dbms_utility.format_error_backtrace);