自治事務執行某些工作(COMMIT，ROLLBACK)后不影響當前的外面執行的事務。

范例
1. 过程中的使用：

非自治事務如下

create or replace procedure sfis1.nonautonomous_insert_goal

as

begin

insert into t values('NONAUTONOMOUS INSERT','1','1');

commit;

end;

自治事務如下

create or replace procedure sfis1.autonomous_insert_goal

as pragma autonomous_transaction;

begin

insert into t values('AUTONOMOUS INSERT','1','1');

commit;

end;

當執行如下（插入兩行）：

begin

insert into t values ('AUTONOMOUS INSERT','1','1');

NONAUTONOMOUS_INSERT_GOAL;

rollback;

end;

當執行下面（只插入一行）

begin

insert into t values ('AUTONOMOUS INSERT','1','1');

AUTONOMOUS_INSERT_GOAL;

rollback; end;

2.trigger 中的使用：

觸發器只commit該觸發的信息，觸發器之外的不會commit

create table emp

as

select * from scott.emp;

create table audit_tab

( username varchar2(30) default user,

timestamp date default sysdate,

msg varchar2(4000))

只有他的老板才能更新他的信息

create or replace trigger sfis1.emp_audit

before update on sfis1.emp for each row

declare

pragma autonomous_transaction;

l_cnt number;

begin

select count(*) into l_cnt from dual

where exists ( select null from emp where empno = :new.empno

start with mgr = ( select empno from emp where ename = user )

connect by prior empno = mgr );

if ( l_cnt = 0 )

then

insert into audit_tab ( msg )values ('Attempt to update'|| :new.empno );

commit;

raise_application_error(-20001,'Access Denied');

end if;

end;

3.對对select查询语句的审计方法

create table sfis1.audit_trail

(username varchar2(30 byte), pk number,attribute varchar2(30 byte),

Dataum varchar2(255 byte),timestamp date)

查詢及記錄用視圖，當查詢含sal, comm, hiredate記錄時插入審計表

create or replace force view sfis1.emp_v

(empno, ename, job, mgr, sal, comm, hiredate, deptno)

as

select empno, ename, job, mgr,

audit_trail_pkg.record (empno, 'sal', sal) sal,

audit_trail_pkg.record (empno, 'comm', comm) comm,

audit_trail_pkg.record (empno, 'hiredate', hiredate) hiredate,

deptno from emp;

CREATE OR REPLACE package SFIS1.audit_trail_pkg

as

function record( p_pk in number,p_attr in varchar2,p_dataum in number ) return number;

function record( p_pk in number,p_attr in varchar2,p_dataum in varchar2 ) return varchar2;

function record( p_pk in number,p_attr in varchar2,p_dataum in date ) return date;

end;

create or replace package body sfis1.audit_trail_pkg

as

procedure log( p_pk in number,p_attr in varchar2,p_dataum in varchar2 )

as

pragma autonomous_transaction;

begin

insert into audit_trail values( user, p_pk, p_attr, p_dataum, sysdate );

commit;

end;

function record( p_pk in number,p_attr in varchar2,p_dataum in number ) return number

is

begin

log( p_pk, p_attr, p_dataum );

return p_dataum;

end;

function record( p_pk in number,p_attr in varchar2,p_dataum in varchar2 ) return varchar2

is

begin

log( p_pk, p_attr, p_dataum );

return p_dataum;

end;

function record( p_pk in number,p_attr in varchar2,p_dataum in date ) return date

is

begin

log( p_pk, p_attr,to_char(p_dataum,'dd.mon.yyyy hh24:mi:ss') );

return p_dataum;

end;

结束自治事务：
为了结束自治事务，必须发出一个完成的COMMIT或ROLLBACK，或执行DDL（隐含COMMIT）。當自主事務做出SET TRANSACTION或SAVEPOINT時，自主事務將自動開始。

ITPub博客

Oracle自治事务autonomous_transaction用法

最新文章