Oracle 嵌套事务与自治事务思考
关键字
? 嵌套事务和自治事务的概念
? 嵌套事务的使用
? 自治事务的使用

一． 概念
1. 嵌套事务(Nested Transaction)：
指在一个Parent事务中嵌套的一个或多个Sub Transaction.并且主事务与其相互影响，这种事务就称为嵌套事务。以Commit作为事务的结束。
2. 自治事务(Autonomous Transaction):
指在function,procedure等subprograms中对事务进行自治管理,当在别的pl/sql block里去调用这些subprograms的时候这些subprograms并不随着父pl/sql block的失败而回滚,而是自己管自己commit。以Commit作为事务的结束。自治事务常用于写入LOG或TRAC信息便于查找错误。
二． 嵌套事务的运用(Nested Transaction)
1．预备Create Table：

create table TEST_POLICY
(
  POLICY_CODE VARCHAR2(20),
  POLICY_TYPE CHAR(1)
)

2.创建一个嵌套事务的procedure:
1)

Procedure P_Insert_Policy(I_Policy_code varchar2(20), 
                            I_Policy_type char(1)) as 
  cnt number :=0;
  begin
      select count(1) into cnt from Test_Policy;
      Dbms_Output.put_line('records of the test_policy is '|| cnt);
      
      Insert into Test_Policy values(I_Policy_code, I_Policy_type);
      commit;--commit in nested transaction 
  end P_Insert_Policy;
--call procedure used in nested transaction
  PROCEDURE TEST_PL_SQL_ENTRY(
                              I_POL_ID IN VARCHAR2,
                              O_SUCC_FLG OUT VARCHAR2) AS
  strSql varchar2(500);
  cnt number := 0;
  BEGIN
     delete from test_policy;
     commit;
     insert into test_policy values('2010042101', '1');
     select count(1) into cnt from Test_Policy;
     Dbms_Output.put_line('records of the test_policy is '|| cnt);
     --call nested transaction
     P_Insert_Policy('2010042102', '2');
     rollback;--rollback data for all transactions
     commit;--master transaction commit
     select count(1) into cnt from Test_Policy;
     Dbms_Output.put_line('records of the test_policy is '|| cnt);
     rollback;
     
     select count(1) into cnt from Test_Policy;
     Dbms_Output.put_line('records of the test_policy is '|| cnt);
     
END TEST_PL_SQL_ENTRY;

=>run Pl/sql:
records of the test_policy is 1 –-主事务中的操作已经commit
records of the test_policy is 1 –-主事务的操作对Nested transaction有影响。
records of the test_policy is 2 –-Nested transaction 已经Commit
records of the test_policy is 2 –-Nested transaction对主事务有影响。

将上面的nested transaction的procedure修改一下，不需要commit:
 

Procedure P_Insert_Policy(I_Policy_code T_contract_master.Policy_Code%type, 
                            I_Policy_type t_contract_master.policy_type%type) as 
  cnt number :=0;
  begin
      select count(1) into cnt from Test_Policy;
      Dbms_Output.put_line('records of the test_policy is '|| cnt);
      
      Insert into Test_Policy values(I_Policy_code, I_Policy_type);
      --commit;
  end P_Insert_Policy;
  PROCEDURE TEST_PL_SQL_ENTRY(
                              I_POL_ID IN VARCHAR2,
                              O_SUCC_FLG OUT VARCHAR2) AS
  strSql varchar2(500);
  cnt number := 0;
  BEGIN
     delete from test_policy;
     commit;
     insert into test_policy values('2010042101', '1');
     select count(1) into cnt from Test_Policy;
     Dbms_Output.put_line('records of the test_policy is '|| cnt);
     
     P_Insert_Policy('2010042102', '2');
     rollback;
     commit;
     select count(1) into cnt from Test_Policy;
     Dbms_Output.put_line('records of the test_policy is '|| cnt);
     rollback;
     
     select count(1) into cnt from Test_Policy;
     Dbms_Output.put_line('records of the test_policy is '|| cnt);
     
END TEST_PL_SQL_ENTRY;
Run Pl/Sql=>
结果是：
records of the test_policy is 1 –-主事务中的操作已经commit
records of the test_policy is 1 –-主事务的操作对Nested transaction有影响。
records of the test_policy is 0 –-Nested transaction 的数据被主事务rollback.
records of the test_policy is 0 

三.自治事务(Autonomous transaction)
1.下面是来自于Oracle上对自治事务的描述：
autonomous transactions  does not depend on the main transaction. For example, if the main transaction rolls back, nested transactions roll back, but autonomous transactions d