autonomous_transaction

在autonomous_transaction程序中执行了insert、update、delete后必须要执行commit或rollback

SQL> create table tmp(content varchar2(100));

Table created


SQL> create or replace function autonomous_transaction_func
  2  return varchar2
  3  is
  4     pragma autonomous_transaction;
  5  begin
  6    insert into tmp values('autonomous_transaction_func');
  7    commit;
  8    return 'autonomous_transcation';
  9  end;
 10  /
Function created


SQL> create or replace procedure non_autonomous_transaction
  2  is
  3  begin
  4    insert into tmp values('non_autonomous_transaction_proc');
  5    commit;
  6  end;
  7  /
Procedure created


SQL> set serveroutput on


SQL> declare
  2    a varchar2(100);
  3  begin
  4    insert into tmp values('main_transaction1');
  5    non_autonomous_transaction;
  6    insert into tmp values('main_transaction2');
  7    a:=autonomous_transaction_func();
  8    dbms_output.put_line(a);
  9    rollback;
 10  end;
 11  /
autonomous_transcation
PL/SQL procedure successfully completed


SQL> select * from tmp;
CONTENT
--------------------------------------------------------------------------------
main_transaction1
non_autonomous_transaction_proc
autonomous_transaction_func


SQL> Create Table tmp(str Varchar2(20)) ;
Table created


SQL> 
SQL> Create or Replace Procedure proc
  2  Is
  3    Pragma Autonomous_Transaction;
  4  Begin
  5    Insert Into tmp Values('a');
  6  End;
  7  /
Procedure created


SQL> 
SQL> Begin
  2    proc;
  3  End;
  4  /
Begin
  proc;
End;
ORA-06519: 检测到活动的独立的事务处理, 已经回退
ORA-06512: 在 "ZLHIS.PROC", line 6
ORA-06512: 在 line 2


SQL> 
SQL> Create or Replace Procedure proc
  2  Is
  3    Pragma Autonomous_Transaction;
  4  Begin
  5    Insert Into tmp Values('a');
  6    Commit;
  7  End;
  8  /
Procedure created


SQL> 
SQL> Begin
  2    proc;
  3  End;
  4  /
PL/SQL procedure successfully completed


SQL> select * from tmp;
STR
--------------------
a