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博客

Pragma RESTRICT_REFERENCES

最新文章