自主事务范围和错误引发

Question

我有些怀疑。让我们假设这个软件包的程序：

PROCEDURE ERR_MANAGER(I_ERRM IN VARCHAR2) IS
BEGIN
    ROLLBACK;
    --DO SOME STUFF
END ERR_MANAGER;

PROCEDURE test IS 
    PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN

    test2;
    COMMIT;
EXCEPTION WHEN OTHERS THEN ERR_MANAGER(SQLERRM);
END test;


PROCEDURE test2 IS
BEGIN

    --DO SOME TRANSACTIONNAL DML

    RAISE_APPLICATION_ERROR(-20001, 'ERR'); --for the test purpose, in reality this could be any actual error

END test2;

您可以看到test2()中存在错误，该错误会上升至test()，然后在err_manager()方法中处理。

所以我有两个问题:

1. err_manager()的范围是什么？它是否仍在自主事务内？我猜应该是，因为它只是一个函数调用，但我想确保
2. 如果由于一个错误而强制退出自主事务，并且没有进行任何提交或回滚，会发生什么情况？

非常感谢。 S.

- Sebas

Answer 1

1. The transaction scope of the execution of the err_manager procedure is the calling autonomous transaction, you are correct.

   Procedures and functions inherit their calling transactions unless they are themselves autonomous transactions.

2. When an autonomous transaction raises an unhandled error, it rollbacks its changes and the error propagates to the calling application. Here's a test:

   SQL> CREATE TABLE t (id number);
   
   Table created.
   
   SQL> DECLARE
     2     l NUMBER;
     3     PROCEDURE p IS
     4        pragma autonomous_transaction;
     5     BEGIN
     6        insert into t values (1);
     7        raise_application_error(-20001, 'rollback?');
     8     END;
     9  BEGIN
    10     p;
    11  EXCEPTION
    12     WHEN OTHERS THEN
    13        DBMS_OUTPUT.put_line('error catched:' || sqlcode);
    14        SELECT COUNT(*) INTO l FROM t;
    15        DBMS_OUTPUT.put_line('lines in t: ' || l);
    16  END;
    17  /
   error catched:-20001
   lines in t: 0
   
   PL/SQL procedure successfully completed.
   

- Vincent Malgrat