存储过程中的pragma autonomous_transaction作用

 

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

 

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

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

自治事务的特点

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

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

 

demo1

//无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;
/
exec parent_block
 
SELECT * FROM t;
//查询结果 
Parent block insert
Child block insert

//有pragma autonomous_transaction

修改在上面 CREATE OR REPLACE PROCEDURE child_block IS 后面添加 pragma autonomous_transaction
//查询结果
Child block insert

  可以看到没有添加pragma autonomous_transaction的话子存储过程中的commit会在父存储过程插入的数据一起提交

 

 

demo2

 

//方法无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
//结果 0 3 6

//方法有pragma autonomous_transaction
修改上面方法howmanyrows在begin前加上pragma autonomous_transaction
结果 0 1 6

可以看到添加了pragma autonomous_transaction后子方法是查不到父存储过程中未提交的数据的