PRAGMA AUTONOMOUS_TRANSACTION

时间：2021-04-29 08:13:04

转自 http://blog.csdn.net/pan_tian/article/details/7675800

这段时间遇到一个问题，程序里明明插入了一条记录，但在后边的一段Procedure中却查不到刚刚插入的记录,最后发现这个Procedure的定义中加入了PRAGMA AUTONOMOUS_TRANSACTION。

PRAGMA AUTONOMOUS_TRANSACTION中文翻译过来叫“自治事务”（翻译的还算好理解），对于定义成自治事务的Procedure，实际上相当于一段独立运行的程序段，这段程序不依赖于主程序，也不干涉主程序

PRAGMA AUTONOMOUS_TRANSACTION

自治事务的特点

第一,这段程序不依赖于原有Main程序，比如Main程序中有未提交的数据，那么在自治事务中是查找不到的。

第二,在自治事务中，commit或者rollback只会提交或回滚当前自治事务中的DML，不会影响到Main程序中的DML。

Autonomous Transaction Demo 1

Without Pragma Autonomous Transaction

  1. CREATE TABLE t (
  2. test_value VARCHAR2(25));
  3. CREATE OR REPLACE PROCEDURE child_block IS
  4. BEGIN
  5. INSERT INTO t
  6. (test_value)
  7. VALUES
  8. ('Child block insert');
  9. COMMIT;
  10. END child_block;
  11. /
  12. CREATE OR REPLACE PROCEDURE parent_block IS
  13. BEGIN
  14. INSERT INTO t
  15. (test_value)
  16. VALUES
  17. ('Parent block insert');
  18. child_block;
  19. ROLLBACK;
  20. END parent_block;
  21. /
  22. -- run the parent procedure
  23. exec parent_block
  24. -- check the results
  25. SELECT * FROM t;
  1. Output:
  2. Parent block insert
  3. Child block insert

With Pragma Autonomous Transaction

  1. CREATE OR REPLACE PROCEDURE child_block IS
  2. PRAGMA AUTONOMOUS_TRANSACTION;
  3. BEGIN
  4. INSERT INTO t
  5. (test_value)
  6. VALUES
  7. ('Child block insert');
  8. COMMIT;
  9. END child_block;
  10. /
  11. CREATE OR REPLACE PROCEDURE parent_block IS
  12. BEGIN
  13. INSERT INTO t
  14. (test_value)
  15. VALUES
  16. ('Parent block insert');
  17. child_block;
  18. ROLLBACK;
  19. END parent_block;
  20. /
  21. -- empty the test table
  22. TRUNCATE TABLE t;
  23. -- run the parent procedure
  24. exec parent_block;
  25. -- check the results
  26. SELECT * FROM t;
  1. Output:
  2. Child block insert

Autonomous Transaction Demo 2

Without Pragma Autonomous Transaction

  1. DROP TABLE t;
  2. CREATE TABLE t (testcol NUMBER);
  3. CREATE OR REPLACE FUNCTION howmanyrows RETURN INTEGER IS
  4. i INTEGER;
  5. BEGIN
  6. SELECT COUNT(*)
  7. INTO i
  8. FROM t;
  9. RETURN i;
  10. END howmanyrows;
  11. /
  12. CREATE OR REPLACE PROCEDURE testproc IS
  13. a INTEGER;
  14. b INTEGER;
  15. c INTEGER;
  16. BEGIN
  17. SELECT COUNT(*)
  18. INTO a
  19. FROM t;
  20. INSERT INTO t VALUES (1);
  21. COMMIT;
  22. INSERT INTO t VALUES (2);
  23. INSERT INTO t VALUES (3);
  24. b := howmanyrows;
  25. INSERT INTO t VALUES (4);
  26. INSERT INTO t VALUES (5);
  27. INSERT INTO t VALUES (6);
  28. COMMIT;
  29. SELECT COUNT(*)
  30. INTO c
  31. FROM t;
  32. dbms_output.put_line(a);
  33. dbms_output.put_line(b);
  34. dbms_output.put_line(c);
  35. END testproc;
  36. /
  37. set serveroutput on
  38. exec testproc
  1. Output:
  2. 0
  3. 3
  4. 6
  5. Total execution time 2.782 sec.

With Pragma Autonomous Transaction

  1. CREATE OR REPLACE FUNCTION howmanyrows RETURN INTEGER IS
  2. i INTEGER;
  3. PRAGMA AUTONOMOUS_TRANSACTION;
  4. BEGIN
  5. SELECT COUNT(*)
  6. INTO i
  7. FROM t;
  8. RETURN i;
  9. END howmanyrows;
  10. /
  11. -- empty the test table
  12. TRUNCATE TABLE t;
  13. exec testproc;
    1. Output:
    2. 0
    3. 1
    4. 6

在触发器中操作触发此触发器的表,用PRAGMA AUTONOMOUS_TRANSACTION选项。

15.1为何使用自治事务 无法回滚的审计

一般情况下利用触发器禁止某些对表的更新等操作时，若记录日志，则触发器最后抛出异常时会造成日志回滚。利用自治事务可防止此点。

避免变异表

即在触发器中操作触发此触发器的表

在触发器中使用DDL 写数据库

对数据库有写操作（INSERT、UPDATE、DELETE、CREATE、ALTER、COMMIT）的存储过程或函数是无法简单的用SQL来调用的，此时可以将其设为自治事务，从而避免ORA-14552（无法在一个查询或DML中执行DDL、COMMIT、ROLLBACK）、ORA-14551（无法在一个查询中执行DML操作）等错误。需要注意的是函数必须有返回值，但仅有IN参数（不能有OUT或IN/OUT参数）。

开发更模块化的代码

在大型开发中，自治事务可以将代码更加模块化，失败或成功时不会影响调用者的其它操作，代价是调用者失去了对此模块的控制，并且模块内部无法引用调用者未提交的数据。

15.2 如何工作 事务控制

DECLARE整个块都是属于父事务的，自治事务从离PRAGMA后的第一个BEGIN开始，只要此BEGIN块仍在作用域，则都属于自治事务。例如在DECLARE模块中声明一个写数据库的函数，则此函数虽然在自治事务所在存储过程执行，但其属于父事务；而自治事务中调用的任何函数和存储过程、激发的任何触发器等均为此自治事务的一部分。

自治事务可以嵌套，嵌套深度等只受INIT.ORA参数TRANSACTIONS（同时并发的事务数，缺省为SESSIONS的1.1倍）制约。

作用域

1. 包中的变量

自治事务可看到并修改父事务的变量，父事务也会察觉到这一改变，且不存在回滚问题。

2. 会话设置/参数

自治事务与父事务共享同一个会话环境，通过ALTER SESSION作的修改对整个会话均有效。但SET TRANSACTION是事务级的，仅对提起修改的事务有效。

3. 数据库修改

父事务已提交的修改对自治事务可见，未提交的对自治事务不可见，自治事务的修改对父事务是否可见取决于隔离级别（Isolation Level）。

对于游标，取决于其打开的位置，若其在父事务中打开，则之前父事务未提交的修改对其是有效的，在自治事务中这些修改也可见；而在自治事务中打开，则父事务未提交的修改不可见。

若使用缺省的READ COMMITTED隔离级别，则自治事务的修改对父事务可见；若改用SERIALIZABLE，则不可见。

4. 锁

父事务与自治事务是完全不同的事务，因此无法共享锁等。

结束一个自治事务必须提交一个COMMIT、ROLLBACK或执行DDL。

保存点无法在自治事务中回滚到父事务中的一个保存点，只能在内部使用保存点。

15.3 最后说明 不支持分布式事务截至8.1.7在自治事务中不支持分布式事务

仅可用PL/SQL 全部事务回滚若自治事务出错，则全部回滚，即便父事务有异常处理模块。

事务级临时表每个会话仅一个事务可访问事务级临时表（多个会话中的事务可并发操作）。

15.4 可能遇到的错误
ORA-06519 – 检查到活动自治事务，回滚——退出自治事务时没有提交、回滚或DDL操作

ORA-14450 – 试图访问正在使用的事务级临时表

ORA-00060 – 等待资源时检查到死锁

在create prodedure 时可以指定PRAGMA AUTONOMOUS_TRANSACTION;

当前的存储过程作为已有事务的子事务运行，子事务的commit，rollback操作不影响父事务的状态

有的时候，希望在select语句中使用自己定义的函数，并且这个函数除了返回特定的值之外，还执行update,insert,delete等操作。

对数据库有写操作（update,insert,delete,crate,alert,commit）的函数是无法简单的用SQL来调用的。

ORA-14551: cannot perform. a DML operation inside a query

如何实现？关键是pragma autonomous_transaction

如下例子：

　　 Create or replace function func_getid return int is
　　2 pragma autonomous_transaction;
　　3 vid int;
　　4 begin
　　5 select max（id） into vid from test_id;
　　6 update test_id set id=id+1;
　　7 commit;
　　8 return vid;
　　9 end;
　　10 /
　　Function created
　　 select func_getid from dual;
　　FUNC_GETID
　　----------
　　1
　　 select func_getid from dual;
　　FUNC_GETID
　　----------
　　2

PRAGMA AUTONOMOUS_TRANSACTION 自治事物

DROP TABLE test_1;

CREATE TABLE test_1 (ID NUMBER PRIMARY KEY , NAME VARCHAR2(20));

CREATE OR REPLACE PROCEDURE test_proce IS
2 PRAGMA AUTONOMOUS_TRANSACTION; -------------自治事物的标示
3 BEGIN
4 INSERT INTO test_1 VALUES(2,'peter');
5 COMMIT;
6 END;
7 /

BEGIN
2 INSERT INTO test_1 VALUES(1,'joe');
3 test_proce; -----------------只COMMIT了自己没有全局COMMIT
4 ROLLBACK; -----------------只有回滚了joe
5 END;
6 /

SELECT * FROM test_1;

ID NAME
---------- --------------------
2 peter

相关文章