一． 概念
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:
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
