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