这段时间遇到一个问题,程序里明明插入了一条记录,但在后边的一段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
With Pragma Autonomous Transaction
-
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:
-
-
3
-
6
-
Total execution time 2.782 sec.
With Pragma Autonomous Transaction
-
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:
-
-
1
-
6
转载请注明出处:http://blog.csdn.net/pan_tian/article/details/7675800