這段時間遇到一個問題，程序里明明插入了一條記錄，但在后邊的一段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:
0
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:
0
1
6
轉載請注明出處:
http://blog.csdn.net/pan_tian/article/details/7675800