PL/SQL异常处理:不执行任何操作(忽略异常) [英] PL/SQL exception handling: do nothing (ignore exception)

问题描述

这是我经常被问到的问题.由于我在stackoverflow上找不到确切的重复项，因此我想将其发布为参考.

This is a question I am asked very frequently. Since I couldn't find any exact duplicate on stackoverflow, I thought I'd post it as a reference.

问题: 在PL/SQL中，我知道如何捕获异常并在捕获异常时执行代码，以及如何将异常传播到调用块. 例如，在以下过程中，直接处理NO_DATA_FOUND异常，同时将所有其他异常引发到调用块:

Question: In PL/SQL, I know how to catch exceptions and execute code when they are caught, and how to propagate them to the calling block. For example, in the following procedure, the NO_DATA_FOUND exception is handled directly, while all other exceptions are raised to the calling block:

CREATE OR REPLACE PROCEDURE MY_PROCEDURE() 
IS
BEGIN
    do_stuff();

EXCEPTION
    WHEN NO_DATA_FOUND THEN
        -- Do something
        handle_exception();

    WHEN OTHERS THEN
        -- Propagate exception
        RAISE;
END;

但是我应该使用什么命令来忽略一个或所有引发的异常，并将执行控制返回给调用块?

But what command should I use to ignore one or all raised exceptions and return execution control back to the calling block?

推荐答案

虽然我同意99％的习惯是在没有至少将日志记录到某个地方的情况下静默忽略异常是一种不好的做法，但在某些特定情况下，这是完全可以接受的.

While I agree that 99% of the time it is bad practice to silently ignore exceptions without at least logging them somewhere, there are specific situations where this is perfectly acceptable.

在这种情况下，NULL是您的朋友:

In these situations, NULL is your friend:

[...]
EXCEPTION

    WHEN OTHERS THEN
        NULL;
END;

可能需要忽略异常的两种典型情况是:

Two typical situations where ignoring exceptions might be desirable are:

1)您的代码包含一条语句，您知道该语句有时会失败，并且您不希望这个事实打断您的程序流. 在这种情况下，您应该将语句括在一个嵌套块中，如以下示例所示:

1) Your code contains a statement which you know will fail occasionally and you don't want this fact to interrupt your program flow. In this case, you should enclose you statement in a nested block, as the following example shows:

CREATE OR REPLACE PROCEDURE MY_PROCEDURE() 
IS
    l_empoyee_name  EMPLOYEES.EMPLOYEE_NAME%TYPE;
BEGIN
    -- Catch potential NO_DATA_FOUND exception and continue
    BEGIN 
        SELECT EMPLOYEE_NAME
        INTO l_empoyee_name
        FROM EMPLOYEES
        WHERE EMPLOYEE_ID = 12345;
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            NULL;
        WHEN OTHERS THEN
            RAISE;
    END;

    do_stuff();

EXCEPTION

    WHEN OTHERS THEN
        -- Propagate exception
        RAISE;
END;

请注意，PL/SQL通常不允许使用Visual Basic中已知的On Error Resume Next类型的异常处理，其中所有异常都将被忽略，程序将继续运行，就好像什么都没发生一样(请参阅

Note that PL/SQL generally does not allow for the On Error Resume Next type of exception handling known from Visual Basic, where all exceptions are ignored and the program continues to run as if nothing happened (see On error resume next type of error handling in PL/SQL oracle). You need to explicitly enclose potentially failing statements in a nested block.

2)您的过程并不重要，以至于忽略它引发的所有异常都不会影响您的主程序逻辑. (但是，这种情况很少见，从长远来看通常会导致调试的噩梦)

2) Your procedure is so unimportant that ignoring all exceptions it throws will not affect your main program logic. (However, this is very rarely the case and can often result in a debugging nightmare in the long run)

BEGIN

    do_stuff();

EXCEPTION

    WHEN OTHERS THEN
        -- Ignore all exceptions and return control to calling block
        NULL;
END;

这篇关于PL/SQL异常处理:不执行任何操作(忽略异常)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！