Pl/SQL 嵌套过程异常处理

这是关于通过多个级别的 PL/SQL 过程进行错误处理的最佳实践问题。我查看了其他一些问题来帮助我解决问题，特别是this one https://stackoverflow.com/questions/7360565/pl-sql-exception-handling-in-procedures.

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

我当前的解决方法如下，但对我来说似乎相当混乱，有很多变量声明。我对 PL/SQL（以及一般的 SQL）非常陌生，所以我希望得到以下方面的建议：

1. 处理多层程序时良好的错误处理技术。
2. 将错误消息反馈到应用程序层（在下面的过程中，由“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 <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;

异常后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"

程序的第三个参数raise_application_error with false值会删除所有先前的错误消息。如果您将在程序中使用错误值p3，您只会看到一条带有代码的错误消息ORA-20003在这个例子中。

P.S.您也可以定义自己的异常并在中使用它们WHEN .. THEN条款。您可以在这里找到更多信息和示例：https://docs.oracle.com/cd/B28359_01/appdev.111/b28370/errors.htm#LNPLS00704 https://docs.oracle.com/cd/B28359_01/appdev.111/b28370/errors.htm#LNPLS00704

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 <one_more_error> then
    <do something>
    log(..., dbms_utility.format_error_backtrace);
    raise_application_error(-20004, dbms_utility.format_error_backtrace);