嵌套事务

Create or replace Procedure Nest_Proc_Training As

pragma autonomous_transaction;          //独立开启事务，相当与spring的TRANSACTION_REQUIRED_NEW

Cnt Int:=0;
Begin
Select Count(*) Into Cnt From Training;
 Cnt := Cnt+1;
Insert Into Training Values(Cnt,Cnt);                             //查看V$transaction，会有两条记录， test_Proc_Training中的事务（insert引起）被挂起
commit;
 Exception When Others Then
 Rollback;
 Dbms_Output.put_line('after rollback is ');
end;



Create or replace Procedure test_Proc_Training
As
Cnt Int:=0;
Begin

Dbms_Output.Put_Line('before commit, the test records count is '|| Cnt);

Select Count(*) Into Cnt From Training;
 Cnt := Cnt+1;
Insert Into Training Values(Cnt,Cnt);

Nest_Proc_Training;
Dbms_Output.Put_Line('after commit, the test records count is '|| Cnt);
Select Count(*) Into Cnt From Training;
 Cnt := Cnt+1;
Insert Into Training Values(Cnt,Cnt);
rollback;
end;



Declare
Begin
test_Proc_Training();
end;