一. 概念
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