#자율 트랜잭션 (autonomous transaction)

트랜잭션 내 트랜잭션 구현
bq. 최상위 익명 PL/SQL 블록, 함수/프로시저, 객체 타입의 메서드, 트리거 에서 활용 가능

Table created.

SQL> create or replace procedure Autonomous_Insert
2 as
3 pragma autonomous_transaction;
4 begin
5 insert into t values ( 'Autonomous Insert' );
6 commit;
7 end;
8 /

Procedure created.

SQL> create or replace procedure NonAutonomous_Insert
2 as
3 begin
4 insert into t values ( 'NonAutonomous Insert' );
5 commit;
6 end;
7 /

Procedure created.

SQL> begin
2 insert into t values ( 'Anonymous Block' );
3 NonAutonomous_Insert;
4 rollback;
5 end;
6 /

PL/SQL procedure successfully completed.

SQL> select * from t;

MSG

SQL> delete from t;

2 row deleted.

SQL> commit;

Commit complete.

SQL> begin
2 insert into t values ( 'Anonymous Block' );
3 Autonomous_Insert;
4 rollback;
5 end;
6 /

PL/SQL procedure successfully completed.

SQL> select * from t;

MSG

|

||Demo#2 (유용한 케이스)||
|{code:sql|borderStyle=solid}
-- 오라클 내부적으로 자율 트랜잭션 사용함 (공간관리, 시퀀스, 감사 등)
SQL> create table error_log
  2  ( ts timestamp,
  3    err1 clob,
  4    err2 clob )
  5  /

Table created.

SQL> create or replace procedure log_error
  2  ( p_err1 in varchar2, p_err2 in varchar2 )
  3  as
  4    pragma autonomous_transaction;
  5  begin
  6    insert into error_log (ts, err1, err2)
  7    values ( systimestamp, p_err1, p_err2);
  8    commit;
  9  end;
/ 10

Procedure created.

SQL> create table t ( x int check ( x > 0 ) );

Table created.

SQL> create or replace procedure p1 ( p_n in number )
  2  as
  3  begin
  4     -- some code here
  5     insert into t (x) values ( p_n );
  6  end;
  7  /

Procedure created.

SQL> create or replace procedure p2 ( p_n in number )
  2  as
  3  begin
  4     -- code
  5     -- code
  6     p1(p_n);
  7  end;
  8  /

Procedure created.

SQL> begin
  2     p2(  1 );
  3     p2(  2 );
  4     p2( -1 );
  5  exception
  6     when others
  7     then
  8        log_error( sqlerrm, dbms_utility.format_error_backtrace );
  9        RAISE;
 10  end;
 11  /
begin
*
ERROR at line 1:
ORA-02290: check constraint (SCOTT.SYS_C00158400) violated
ORA-06512: at line 9


SQL> select * from t;

no rows selected

SQL> rollback;

Rollback complete.

SQL> select * from error_log;

TS
---------------------------------------------------------------------------
ERR1
--------------------------------------------------------------------------------
ERR2
--------------------------------------------------------------------------------
16-OCT-12 10.14.04.463288 PM
ORA-02290: check constraint (SCOTT.SYS_C00158400) violated
ORA-06512: at "SCOTT.P1", line 5
ORA-06512: at "SCOTT.P2", line 6
ORA-06512: at

|