Oracle自治事务autonomous_transaction怎么用

这篇文章主要为大家展示了“Oracle自治事务autonomous_transaction怎么用”，内容简而易懂，条理清晰，希望能够帮助大家解决疑惑，下面让小编带领大家一起研究并学习一下“Oracle自治事务autonomous_transaction怎么用”这篇文章吧。范例
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
hh34:mi:ss’) );
return
p_dataum;
end;
end;
结束自治事务：
为了结束自治事务，必须发出一个完成的COMMIT或ROLLBACK，或执行DDL（隐含COMMIT）。當自主事務做出SET
TRANSACTION或SAVEPOINT時，自主事務將自動開始。以上是“Oracle自治事务autonomous_transaction怎么用”这篇文章的所有内容，感谢各位的阅读！相信大家都有了一定的了解，希望分享的内容对大家有所帮助，如果还想学习更多知识，欢迎关注云技术行业资讯频道！

相关推荐: MySQL如何开启MySQL慢查询日志及分析工具mysqldumpslow

这篇文章给大家分享的是有关MySQL如何开启MySQL慢查询日志及分析工具mysqldumpslow的内容。小编觉得挺实用的，因此分享给大家做个参考，一起跟随小编过来看看吧。 1. 启用MySQL的慢查询日志很简单,只需在MySQL的配置文件my.cnf里添加…