2009/11/19

Oracle PL/SQL 中, 利用 pragma AUTONOMOUS_TRANSACTION 讓不同的程序, 各自獨立 Commit 或 Rollback




在 Oracle PL/SQL 交易中, 常會遇到下面情況 :


A Procedure 呼叫 B Procedure (B Procedure 裡面有做一些 DML 且 commit),

但是 A Procedure 之後可能因某些條件而導致 Rollback 時,

則 A Procedure 呼叫 B Procedure 之前的交易不會被 Rollback 回去,

該如何 Rollback A Procedure 的所有交易, 但保留 B Procedure 的交易呢 ?

使用 pragma AUTONOMOUS_TRANSACTION 來達成此需求,

 "不使用" pragma AUTONOMOUS_TRANSACTION 範例
--建立 Temp Table
create tom1 (
aa varchar2(100);
);

--建立 Temp 程序
create or replace procedure tomPro
is
begin
insert into tom1 values('123');
commit;
end;

--執行
begin
insert into tom1 values( 'abc' );
tomPro;
insert into tom1 values( 'xyz' );
rollback;
end;

--查看 Temp Table 結果
abc
123


 "使用" pragma AUTONOMOUS_TRANSACTION 範例
--建立 Temp Table
create tom1 (
aa varchar2(100);
);

--建立 Temp 程序
create or replace procedure tomPro
is
pragma AUTONOMOUS_TRANSACTION;
begin
insert into tom1 values('123');
commit;
end;

--執行
begin
insert into tom1 values( 'abc' );
tomPro;
insert into tom1 values( 'xyz' );
rollback;
end;

--查看 Temp Table 結果
123