在SELECT INTO中使用时不会引发NO_DATA_FOUND异常

问题描述

从PLSQL中使用的函数抛出异常时，我注意到NO_DATA_FOUND异常的奇怪行为.

I noticed strange behaviour of NO_DATA_FOUND exception when thrown from function used in PLSQL.

长话短说-在使用赋值时，它确实从函数传播，并且在SELECT INTO中使用时，它不会传播(或在两者之间的某个位置进行无声处理).

Long story short - it does propagate from function when using assignment, and does not propagate (or is handled silently somewhere in between) when used in SELECT INTO.

因此，给定函数test_me调用时抛出NO_DATA_FOUND异常:

So, given function test_me throwing NO_DATA_FOUND exception, when invoked as:

v_x := test_me(p_pk);

当以以下方式调用时，它将引发异常:

It throws an exception, while when invoked as:

SELECT test_me(p_pk) INTO v_x FROM dual;

它不会引发异常.除其他例外，不会发生这种情况.您可以在下面找到我的测试示例.

it does not throw exception. This does not occur with other exceptions. Below You can find my test examples.

有人可以向我解释这种行为吗?

Could somebody please explain to me this behaviour?

set serveroutput on;
CREATE OR REPLACE FUNCTION test_me(p_pk NUMBER) RETURN NVARCHAR2
IS
    v_ret NVARCHAR2(50 CHAR);
BEGIN
    BEGIN
        SELECT 'PYK' INTO v_ret FROM dual WHERE 1 = 1/p_pk;
    EXCEPTION WHEN NO_DATA_FOUND THEN
        dbms_output.put_line(chr(9)||chr(9)||chr(9)||' (test_me NO_DATA_FOUND handled and rerised)');
        RAISE;
    END;
    RETURN v_ret;
END;
/
DECLARE
    v_x NVARCHAR2(500 CHAR);
    v_pk NUMBER;
    PROCEDURE test_example(p_pk NUMBER)
    IS
    BEGIN
        BEGIN
            dbms_output.put_line(chr(9)||chr(9)||'Test case 1: Select into.');
            SELECT test_me(p_pk) INTO v_x FROM dual;
            dbms_output.put_line(chr(9)||chr(9)||'Success: '||NVL(v_x,'NULL RETURNED'));
        EXCEPTION
            WHEN NO_DATA_FOUND THEN
                dbms_output.put_line(chr(9)||chr(9)||'Failure: NO_DATA_FOUND detected');
            WHEN OTHERS THEN
                dbms_output.put_line(chr(9)||chr(9)||'Failure: '||SQLCODE||' detected');
        END;
        dbms_output.put_line(' ');
        BEGIN
            dbms_output.put_line(chr(9)||chr(9)||'Test case 2: Assignment.');
            v_x := test_me(p_pk);
            dbms_output.put_line(chr(9)||chr(9)||'Success: '||NVL(v_x,'NULL RETURNED'));
        EXCEPTION
            WHEN NO_DATA_FOUND THEN
                dbms_output.put_line(chr(9)||chr(9)||'Failure: NO_DATA_FOUND detected');
            WHEN OTHERS THEN
                dbms_output.put_line(chr(9)||chr(9)||'Failure: '||SQLCODE||' detected');
        END;
    END;
BEGIN
    dbms_output.put_line('START');
    dbms_output.put_line(' ');
    dbms_output.put_line(chr(9)||'Example 1: Function throws some exception, both cases throws exception, everything is working as expected.');
    test_example(0);
    dbms_output.put_line(' ');
    dbms_output.put_line(chr(9)||'Example 2: Query returns row, there is no exceptions, everything is working as expected.');
    test_example(1);
    dbms_output.put_line(' ');
    dbms_output.put_line(chr(9)||'Example 3: Query inside function throws NO_DATA_FOUND, strange things happen - one case is throwing exception, the other is not.');
    test_example(2);
    dbms_output.put_line(' ');
    dbms_output.put_line('END');
END;
/
DROP FUNCTION test_me;

推荐答案

一个最小的示例是:

CREATE FUNCTION raise_exception RETURN INT
IS
BEGIN
  RAISE NO_DATA_FOUND;
END;
/

如果您这样做:

SELECT raise_exception
FROM   DUAL;

您将获得包含NULL值的一行-状态:

You will get a single row containing a NULL value - Ask Tom states:

，然后跟进:

在sql中，找不到任何数据，只是简单地表示没有找到数据"，停止.

in sql, no data found quite simply means "no data found", stop.

在幕后，SQL重新引发了客户端应用程序嘿，哥们-no_data_found".这 客户端在这种情况下说:啊哈，找不到数据意味着'数据结束'"，然后停止.

Under the covers, SQL is raising back to the client application "hey buddy -- no_data_found". The client in this case says "ah hah, no data found means 'end of data'" and stops.

因此，在函数中引发了异常，SQL客户端看到了这一点并将其解释为没有数据，该数据是NULL值，并且处理"了异常.

So the exception is raised in the function and the SQL client sees this and interprets this as there is no data which is a NULL value and "handles" the exception.

所以

DECLARE
  variable_name VARCHAR2(50);
BEGIN
  SELECT raise_exception
  INTO   variable_name
  FROM   DUAL
END;
/

将成功，因为DUAL表只有一行，并且将(静默地)处理该函数的异常，并且该变量最终将包含一个NULL值.

Will succeed as the DUAL table has a single row and the exception from the function will be handled (silently) and the variable will end up containing a NULL value.

但是，

BEGIN
  DBMS_OUTPUT.PUT_LINE( raise_exception );
END;
/

这次是异常从函数传递到PL/SQL范围-它不处理错误，并将异常传递给异常处理程序块(不存在)，然后传递给应用程序范围并终止程序的执行.

The exception is this time being passed from the function to a PL/SQL scope - which does not handle the error and passes the exception to the exception handler block (which does not exist) so then gets passed up to the application scope and terminates execution of the program.

问汤姆:

您将看到-这完全是CLIENT解释ORA-xxxxx消息的方式.当SQL发出该消息时，客户端将其解释为您已完成".另一方面，该消息由PLSQL提出但未由PLSQL程序员处理时，被解释为刚刚发生的一件坏事"

You see -- it is all in the way the CLIENT interprets the ORA-xxxxx message. That message, when raised by SQL, is interpreted by the client as "you are done". That message, when raised by PLSQL and not handled by the PLSQL programmer, is on the other hand interpreted as "a bad thing just happened"

PLSQL和SQL实际上都在这里做同样的事情.正是客户决定采取其他措施.

Both PLSQL and SQL actually do the same thing here. It is the CLIENT that is deciding to do something different.

现在，如果我们更改功能以引发其他异常:

Now, if we change the function to raise a different exception:

CREATE OR REPLACE FUNCTION raise_exception RETURN INT
IS
BEGIN
  RAISE ZERO_DIVIDE;
END;
/

然后两者:

SELECT raise_exception
FROM   DUAL;

和:

BEGIN
  DBMS_OUTPUT.PUT_LINE( raise_exception );
END;
/

不知道如何处理异常并以ORA-01476 divisor is equal to zero终止.

do not know how to handle the exception and terminate with ORA-01476 divisor is equal to zero.

这篇关于在SELECT INTO中使用时不会引发NO_DATA_FOUND异常的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！