PRAGMA AUTONOMOUS_TRANSACTION

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

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

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

自治事务的特点

第一,这段程序不依赖于原有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.   
  4. CREATE OR REPLACE PROCEDURE child_block IS  
  5.   
  6. BEGIN  
  7.   INSERT INTO t  
  8.   (test_value)  
  9.   VALUES  
  10.   ('Child block insert');  
  11.   COMMIT;  
  12. END child_block;  
  13. /  
  14.   
  15. CREATE OR REPLACE PROCEDURE parent_block IS  
  16.   
  17. BEGIN  
  18.   INSERT INTO t  
  19.   (test_value)  
  20.   VALUES  
  21.   ('Parent block insert');  
  22.   
  23.    child_block;  
  24.   
  25.    ROLLBACK;  
  26. END parent_block;  
  27. /  
  28.   
  29. -- run the parent procedure  
  30. exec parent_block  
  31.   
  32. -- check the results  
  33. 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.   
  3. PRAGMA AUTONOMOUS_TRANSACTION;  
  4.   
  5. BEGIN  
  6.   INSERT INTO t  
  7.   (test_value)  
  8.   VALUES  
  9.   ('Child block insert');  
  10.   
  11.   COMMIT;  
  12. END child_block;  
  13. /  
  14.   
  15. CREATE OR REPLACE PROCEDURE parent_block IS  
  16.   
  17. BEGIN  
  18.   INSERT INTO t  
  19.   (test_value)  
  20.   VALUES  
  21.   ('Parent block insert');  
  22.   
  23.    child_block;  
  24.   
  25.    ROLLBACK;  
  26. END parent_block;  
  27. /  
  28. -- empty the test table  
  29. TRUNCATE TABLE t;  
  30.   
  31. -- run the parent procedure  
  32. exec parent_block;  
  33.   
  34. -- check the results  
  35. SELECT * FROM t;  
  1. Output:  
  2. Child block insert  

 

 

Autonomous Transaction Demo 2

Without Pragma Autonomous Transaction

 

 

  1. DROP TABLE t;  
  2.   
  3. CREATE TABLE t (testcol NUMBER);  
  4.   
  5. CREATE OR REPLACE FUNCTION howmanyrows RETURN INTEGER IS  
  6.  i INTEGER;  
  7. BEGIN  
  8.   SELECT COUNT(*)  
  9.   INTO i  
  10.   FROM t;  
  11.   
  12.   RETURN i;  
  13. END howmanyrows;  
  14. /  
  15.   
  16. CREATE OR REPLACE PROCEDURE testproc IS  
  17.  a INTEGER;  
  18.  b INTEGER;  
  19.  c INTEGER;  
  20. BEGIN  
  21.   SELECT COUNT(*)  
  22.   INTO a  
  23.   FROM t;  
  24.   
  25.   INSERT INTO t VALUES (1);  
  26.   COMMIT;  
  27.   
  28.   INSERT INTO t VALUES (2);  
  29.   INSERT INTO t VALUES (3);  
  30.   
  31.   b := howmanyrows;  
  32.   
  33.   INSERT INTO t VALUES (4);  
  34.   INSERT INTO t VALUES (5);  
  35.   INSERT INTO t VALUES (6);  
  36.   COMMIT;  
  37.   
  38.   SELECT COUNT(*)  
  39.   INTO c  
  40.   FROM t;  
  41.   
  42.   dbms_output.put_line(a);  
  43.   dbms_output.put_line(b);  
  44.   dbms_output.put_line(c);  
  45. END testproc;  
  46. /  
  47.   
  48. set serveroutput on  
  49.   
  50. 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.   
  4.  PRAGMA AUTONOMOUS_TRANSACTION;  
  5. BEGIN  
  6.   SELECT COUNT(*)  
  7.   INTO i  
  8.   FROM t;  
  9.   
  10.   RETURN i;  
  11. END howmanyrows;  
  12. /  
  13.   
  14. -- empty the test table  
  15. TRUNCATE TABLE t;  
  16.   
  17. 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

 

posted @ 2014-12-31 14:36  princessd8251  阅读(3671)  评论(0)  编辑  收藏  举报