Pragma RESTRICT_REFERENCES-优快云博客

博客介绍了从SQL调用函数时要进行'purity'检测，从9i开始在函数运行时检测，可使用Pragma RESTRICT_REFERENCES指示编译器检测。文中给出示例，展示运行出错情况，添加'purity' check仍有问题，最后介绍使用自治事务解决问题。

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

To be callable from SQL statements, a stored function must obey the following "purity" rules, which are meant to control side effects:
When called from a SELECT statement or a parallelized INSERT, UPDATE, or DELETE statement, the function cannot modify any database tables.

When called from an INSERT, UPDATE, or DELETE statement, the function cannot query or modify any database tables modified by that statement.

When called from a SELECT, INSERT, UPDATE, or DELETE statement, the function cannot execute SQL transaction control statements (such as COMMIT), session control statements (such as SET ROLE), or system control statements (such as ALTER SYSTEM). Also, it cannot execute DDL statements (such as CREATE) because they are followed by an automatic commit

从sql调用函数是要遵循以上规则的，也就是要进行'purity'检测，从9i开始在函数运行时进行检测，通过使用Pragma RESTRICT_REFERENCES 指示编译器进行某些'purity level'的检测：

WNDS: writes no database state (does not modify database tables).

WNPS: writes no package state (does not modify packaged variables).

RNDS: reads no database state (does not query database tables).

RNPS: reads no package state (does not reference package variables).

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

Package created.

SQL> create or replace package body pkg_pragma is
2 function fn_pragma return number
3 is
4 begin
5 insert into t_pragma values(systimestamp);
6 commit;
7 return 1;
8 end;
9 end pkg_pragma;
10 /

Package body created.

运行时出错

SQL> select pkg_pragma.fn_pragma() test from dual;
select pkg_pragma.fn_pragma() test from dual
*
ERROR at line 1:
ORA-14551: cannot perform a DML operation inside a query
ORA-06512: at "DRAGONDB.PKG_PRAGMA", line 5

添加'purity' check,指示编译器检测

SQL> create or replace package pkg_pragma is
2
3 function fn_pragma return number;
4 pragma restrict_references(fn_pragma,wnds);
5
6 end pkg_pragma;
7 /

Package created.

SQL> create or replace package body pkg_pragma is
2
3 function fn_pragma return number
4 is
5 begin
6 insert into t_pragma values(systimestamp);
7 commit;
8 return 1;
9 end;
10
11 end pkg_pragma;
12 /

Warning: Package Body created with compilation errors.

SQL> show error
Errors for PACKAGE BODY PKG_PRAGMA:

LINE/COL ERROR
-------- -----------------------------------------------------------------
3/3 PLS-00452: Subprogram 'FN_PRAGMA' violates its associated pragma

这种情况使用自治事务:

SQL> create or replace package pkg_pragma is
2
3 function fn_pragma return number;
4
5 end pkg_pragma;
6 /

Package created.

SQL> create or replace package body pkg_pragma is
2
3 function fn_pragma return number
4 is
5 pragma autonomous_transaction;
6 begin
7 insert into t_pragma values(systimestamp);
8 commit;
9
10 return 1;
11 end;
12
13 end pkg_pragma;
14 /

Package body created.

SQL> select pkg_pragma.fn_pragma() test from dual;

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

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/45259/viewspace-134956/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/45259/viewspace-134956/