Shulou(Shulou.com)05/31 Report--

This article mainly shows you "how to use Oracle autonomous transaction". The content is simple and easy to understand, and the organization is clear. I hope it can help you solve your doubts. Let Xiaobian lead you to study and learn this article "how to use Oracle autonomous transaction".

example

1. In-process use:

Non-self-governing affairs are as follows

create or replace procedure sfis1.nonautonomous_insert_goal

as

begin

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

commit;

end;

Autonomous affairs are as follows

create or replace procedure sfis1.autonomous_insert_goal

as pragma autonomous_transaction;

begin

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

commit;

end;

When executed as follows (insert two lines):

begin

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

NONAUTONOMOUS_INSERT_GOAL;

rollback;

end;

When executed below (insert only one line)

begin

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

AUTONOMOUS_INSERT_GOAL;

rollback; end;

2. Use of trigger:

Triggers commit only the information that triggers, nothing outside triggers commits.

create table emp

as

select * from scott.emp;

create table audit_tab

( username varchar2(30) default user,

timestamp date default sysdate,

msg varchar2(4000))

Only his boss can update his profile.

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. Audit methods for select query statements

create table sfis1.audit_trail

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

Dataum varchar2(255 byte),timestamp date)

View for querying and recording, insert audit table when querying records containing 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 hh34:mi:ss') );

return p_dataum;

end;

end;

End of autonomous affairs:

In order to end an autonomous transaction, either a complete COMMIT or ROLLBACK must be issued, or DDL must be executed (implicit COMMIT). When an autonomous transaction makes a SET TRANSACTION or SAVEPOINT, the autonomous transaction automatically starts.

The above is "Oracle autonomous transaction how to use" all the content of this article, thank you for reading! I believe that everyone has a certain understanding, hope to share the content to help everyone, if you still want to learn more knowledge, welcome to pay attention to the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.