问题描述
这是有关通过多个级别的PL/SQL过程进行错误处理的最佳实践问题.我查看了其他一些问题来帮助我,特别是此问题 .
当前,我有一个带有过程1的程序,该程序调用过程2,其调用过程3.我试图执行适当的错误处理-但我想最终将确切的问题输出回应用程序层.我希望对如何有效,清楚地做到这一点有所了解.
下面是我当前的解决方法,但是对我来说,它似乎很杂乱,带有很多变量声明.我对PL/SQL(和一般来说的SQL)非常陌生,因此,我很感谢以下建议:
Program Flow: UI -> Proc 1 -> Proc 2 -> Proc 3
--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...
-- 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;
--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 <some_error> then
<do something>
-- 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 <another_error> then
<do something>
-- 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 <another_error> then
<do something>
-- 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 <one_more_error> then
<do something>
raise_application_error(-20004, dbms_utility.format_error_backtrace);
end;
After exception in p1
you will see something like this:
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
中使用假值,则在此示例中,您只会看到一条错误消息,代码为ORA-20003
.
P. S.同样,您可以定义自己的异常,并在WHEN .. THEN
子句中使用它们.在这里您可以找到更多信息和示例: https://docs .oracle.com/cd/B28359_01/appdev.111/b28370/errors.htm#LNPLS00704
P. S. Also you can define your own exceptions and use them in WHEN .. THEN
clause. Here you find more information and examples: https://docs.oracle.com/cd/B28359_01/appdev.111/b28370/errors.htm#LNPLS00704
P. P. S.如何记录.记录过程:
P. P. S. How to log. Log procedure:
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 <one_more_error> then
<do something>
log(..., dbms_utility.format_error_backtrace);
raise_application_error(-20004, dbms_utility.format_error_backtrace);
这篇关于Pl/SQL嵌套过程异常处理的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!