本文介绍了Pl/SQL嵌套过程异常处理的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

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

This is a best practice question on error handling through multiple levels of PL/SQL procedures. I've looked at a few other questions to help me out, in particular this one.

当前,我有一个带有过程1的程序,该程序调用过程2,其调用过程3.我试图执行适当的错误处理-但我想最终将确切的问题输出回应用程序层.我希望对如何有效,清楚地做到这一点有所了解.

Currently, I have a program with Procedure 1, which calls Procedure 2, which calls Procedure 3. I'm trying to perform adequate error handling - but I'd like to output eventually the exact problem back to the application layer. I'm hoping to get some ideas on how I can do this efficiently and clearly.

下面是我当前的解决方法,但是对我来说,它似乎很杂乱,带有很多变量声明.我对PL/SQL(和一般来说的SQL)非常陌生,因此,我很感谢以下建议:

My current solution method is below, but it seems rather messy to me, with lots of variable declarations. I am very new to PL/SQL (and SQL in general) so I'd appreciate any advice on:

  1. 当处理多层过程时,良好的错误处理技术.
  2. 将错误消息反馈到应用程序层(在下面的过程中,由"out_overall_output"变量表示.

程序流程:用户界面->处理1->处理2->处理3

Program Flow: 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;

注意:过程调用的级别比这更深.我展示的代码片段已大大简化.在我的实际过程中,错误消息和变量名更具描述性.

Note: The levels of procedure calling goes deeper than this. The snippets I have shown are greatly simplified. The error messages and variable names in my real procedures are more descriptive.

推荐答案

要在应用程序级别显示服务器发生的情况"的确切说明,可以尝试以下方法.在过程中:

To show exact explanations of "what happens with a server" for application level you can try following. In procedures:

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中出现异常后,您将看到类似以下内容的内容:

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.

Third parameter of procedure raise_application_error with false value cuts all previous error messages. If you will use false value in procedure p3, you will see only one error message with code ORA-20003 in this example.

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嵌套过程异常处理的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

08-18 21:54
查看更多