pragma autonomous_transaction : restrict_references(<fuctionname>,wnds) and serially

本文链接：https://blog.youkuaiyun.com/qindalin/article/details/8814921

本文通过创建并使用 Oracle PL/SQL 包演示了如何实现串行可重用性和自治事务特性。首先定义了一个串行可重用包 pkg1 来管理变量状态，并展示其在不同会话中的行为。接着创建了一个名为 pkg2 的包，该包包含一个自治事务函数 fn_auto，用于在查询中执行 DML 操作。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

pragma : serially_reusable

SQL> CREATE PACKAGE pkg1 IS

PRAGMA SERIALLY_REUSABLE;
num NUMBER := 0;
PROCEDURE init_pkg_state(n NUMBER);
PROCEDURE print_pkg_state;
END pkg1;
/ 2 3 4 5 6 7

Package created.

SQL> CREATE PACKAGE BODY pkg1 IS
PRAGMA SERIALLY_REUSABLE;
PROCEDURE init_pkg_state (n NUMBER) IS
BEGIN
pkg1.num := n;
END;
PROCEDURE print_pkg_state IS
BEGIN
dbms_output.put_line('Num: ' || pkg1.num);
END;
END pkg1;
/ 2 3 4 5 6 7 8 9 10 11 12

Package body created.

SQL>
SQL>
SQL> exec pkg1.init_pkg_state(10);

PL/SQL procedure successfully completed.

SQL> exec pkg1.print_pkg_state;
Num: 0

PL/SQL procedure successfully completed.

pragma :autonomous_transaction

SQL> create table t(ab date);

Table created.

SQL> create or replace package pkg2 is
function fn_auto return number;
end pkg2;
/ 2 3 4

Package created.

SQL> create or replace package body pkg2 is
function fn_auto return number
is
begin
insert into t values(sysdate);
commit;
return 1;
end;
end pkg2;
/ 2 3 4 5 6 7 8 9 10

Package body created.

SQL> select pkg2.fn_auto() test from dual;
select pkg2.fn_auto() test from dual
*
ERROR at line 1:
ORA-14551: cannot perform a DML operation inside a query
ORA-06512: at "QDL.PKG2", line 5

SQL> create or replace package body pkg2 is
function fn_auto return number
is
pragma autonomous_transaction;
begin
insert into t values(systimestamp);
commit;
return 1;
end;
end pkg2;
/ 2 3 4 5 6 7 8 9 10 11

Package body created.

SQL> select pkg2.fn_auto() test from dual;

TEST
----------
1

pragma :restrict_references(fn_auto,wnds)

make the compiler compile the function fn_auto according to WNDS (write no database ) , so after the package boday created, you see the compile error directly.