技术开发 频道

Oracle:嵌套事务与自治事务的思考

  【IT168技术】首先介绍一些相关的基本概念

  嵌套事务(Nested Transaction):指在一个Parent事务中嵌套的一个或多个Sub Transaction.并且主事务与其相互影响,这种事务就称为嵌套事务。以Commit作为事务的结束。

  自治事务(Autonomous Transaction):指在function,procedure等subprograms中对事务进行自治管理,当在别的pl/sql block里去调用这些subprograms的时候这些subprograms并不随着父pl/sql block的失败而回滚,而是自己管自己commit。以Commit作为事务的结束。自治事务常用于写入LOG或TRAC信息便于查找错误。

  嵌套事务的运用(Nested Transaction)

  预备Create Table:

1.create table TEST_POLICY  
2.(  
3.  POLICY_CODE VARCHAR2(20),  
4.  POLICY_TYPE CHAR(1)  
5.)  

  创建一个嵌套事务的procedure:

1.Procedure P_Insert_Policy(I_Policy_code varchar2(20),    
2.                            I_Policy_type char(1)) as    
3.  cnt number :=0;  
4.  begin  
5.      select count(1) into cnt from Test_Policy;  
6.      Dbms_Output.put_line('records of the test_policy is '|| cnt);  
7.        
8.      Insert into Test_Policy values(I_Policy_code, I_Policy_type);  
9.      commit;--commit in nested transaction    
10.  end P_Insert_Policy;  
11.--call procedure used in nested transaction  
12.  PROCEDURE TEST_PL_SQL_ENTRY(  
13.                              I_POL_ID IN VARCHAR2,  
14.                              O_SUCC_FLG OUT VARCHAR2) AS  
15.  strSql varchar2(500);  
16.  cnt number := 0;  
17.  BEGIN  
18.     delete from test_policy;  
19.     commit;  
20.     insert into test_policy values('2010042101', '1');  
21.     select count(1) into cnt from Test_Policy;  
22.     Dbms_Output.put_line('records of the test_policy is '|| cnt);  
23.     --call nested transaction  
24.     P_Insert_Policy('2010042102', '2');  
25.     rollback;--rollback data for all transactions  
26.     commit;--master transaction commit  
27.     select count(1) into cnt from Test_Policy;  
28.     Dbms_Output.put_line('records of the test_policy is '|| cnt);  
29.     rollback;  
30.        
31.     select count(1) into cnt from Test_Policy;  
32.     Dbms_Output.put_line('records of the test_policy is '|| cnt);  
33.        
34.END TEST_PL_SQL_ENTRY;  
35.  
36.=>run Pl/sql:  
37.records of the test_policy is 1-主事务中的操作已经commit  
38.records of the test_policy is 1-主事务的操作对Nested transaction有影响。  
39.records of the test_policy is 2-Nested transaction 已经Commit  
40.records of the test_policy is 2-Nested transaction对主事务有影响。  

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

1.Procedure P_Insert_Policy(I_Policy_code T_contract_master.Policy_Code%type,    
2.                            I_Policy_type t_contract_master.policy_type%type) as    
3.  cnt number :=0;  
4.  begin  
5.      select count(1) into cnt from Test_Policy;  
6.      Dbms_Output.put_line('records of the test_policy is '|| cnt);  
7.        
8.      Insert into Test_Policy values(I_Policy_code, I_Policy_type);  
9.      --commit;  
10.  end P_Insert_Policy;  
11.  PROCEDURE TEST_PL_SQL_ENTRY(  
12.                              I_POL_ID IN VARCHAR2,  
13.                              O_SUCC_FLG OUT VARCHAR2) AS  
14.  strSql varchar2(500);  
15.  cnt number := 0;  
16.  BEGIN  
17.     delete from test_policy;  
18.     commit;  
19.     insert into test_policy values('2010042101', '1');  
20.     select count(1) into cnt from Test_Policy;  
21.     Dbms_Output.put_line('records of the test_policy is '|| cnt);  
22.        
23.     P_Insert_Policy('2010042102', '2');  
24.     rollback;  
25.     commit;  
26.     select count(1) into cnt from Test_Policy;  
27.     Dbms_Output.put_line('records of the test_policy is '|| cnt);  
28.     rollback;  
29.        
30.     select count(1) into cnt from Test_Policy;  
31.     Dbms_Output.put_line('records of the test_policy is '|| cnt);  
32.        
33.END TEST_PL_SQL_ENTRY;  
34.Run Pl/Sql=>  
35.结果是:  
36.records of the test_policy is 1-主事务中的操作已经commit  
37.records of the test_policy is 1-主事务的操作对Nested transaction有影响。  
38.records of the test_policy is 0-Nested transaction 的数据被主事务rollback.  
39.records of the test_policy is 0  
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
0
相关文章