原帖地址http://blog.youkuaiyun.com/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
- CREATE TABLE t (
- test_value VARCHAR2(25));
- CREATE OR REPLACE PROCEDURE child_block IS
- BEGIN
- INSERT INTO t
- (test_value)
- VALUES
- ('Child block insert');
- COMMIT;
- END child_block;
- /
- CREATE OR REPLACE PROCEDURE parent_block IS
- BEGIN
- INSERT INTO t
- (test_value)
- VALUES
- ('Parent block insert');
- child_block;
- ROLLBACK;
- END parent_block;
- /
- -- run the parent procedure
- exec parent_block
- -- check the results
- SELECT * FROM t;
- Output:
- Parent block insert
- Child block insert
- CREATE OR REPLACE PROCEDURE child_block IS
- PRAGMA AUTONOMOUS_TRANSACTION;
- BEGIN
- INSERT INTO t
- (test_value)
- VALUES
- ('Child block insert');
- COMMIT;
- END child_block;
- /
- CREATE OR REPLACE PROCEDURE parent_block IS
- BEGIN
- INSERT INTO t
- (test_value)
- VALUES
- ('Parent block insert');
- child_block;
- ROLLBACK;
- END parent_block;
- /
- -- empty the test table
- TRUNCATE TABLE t;
- -- run the parent procedure
- exec parent_block;
- -- check the results
- SELECT * FROM t;
- Output:
- Child block insert
Autonomous Transaction Demo 2
Without Pragma Autonomous Transaction
- DROP TABLE t;
- CREATE TABLE t (testcol NUMBER);
- CREATE OR REPLACE FUNCTION howmanyrows RETURN INTEGER IS
- i INTEGER;
- BEGIN
- SELECT COUNT(*)
- INTO i
- FROM t;
- RETURN i;
- END howmanyrows;
- /
- CREATE OR REPLACE PROCEDURE testproc IS
- a INTEGER;
- b INTEGER;
- c INTEGER;
- BEGIN
- SELECT COUNT(*)
- INTO a
- FROM t;
- INSERT INTO t VALUES (1);
- COMMIT;
- INSERT INTO t VALUES (2);
- INSERT INTO t VALUES (3);
- b := howmanyrows;
- INSERT INTO t VALUES (4);
- INSERT INTO t VALUES (5);
- INSERT INTO t VALUES (6);
- COMMIT;
- SELECT COUNT(*)
- INTO c
- FROM t;
- dbms_output.put_line(a);
- dbms_output.put_line(b);
- dbms_output.put_line(c);
- END testproc;
- /
- set serveroutput on
- exec testproc
- Output:
- 0
- 3
- 6
- Total execution time 2.782 sec.
- CREATE OR REPLACE FUNCTION howmanyrows RETURN INTEGER IS
- i INTEGER;
- PRAGMA AUTONOMOUS_TRANSACTION;
- BEGIN
- SELECT COUNT(*)
- INTO i
- FROM t;
- RETURN i;
- END howmanyrows;
- /
- -- empty the test table
- TRUNCATE TABLE t;
- exec testproc;
- Output:
- 0
- 1
- 6