SQL> create or replace function f_stu
2 return varchar2
3 is
4 username varchar2(20);
5 predicate varchar2(100);
6 begin
7 insert into emp1(empno,ename) values(1235,'Zhao');
8 commit;
9 return 'ok';
10 end;
11 /
函数已创建。
SQL> select f_stu() from dual;
select f_stu() from dual
*
第 1 行出现错误:
ORA-14551: 无法在查询中执行 DML 操作
ORA-06512: 在 "SCOTT.F_STU", line 7
SQL> create or replace function f_stu
2 return varchar2
3 is
4 pragma autonomous_transaction;
5 username varchar2(20);
6 predicate varchar2(100);
7 begin
8 insert into emp1(empno,ename) values(1235,'Zhao');
9 commit;
10 return 'ok';
11 end;
12 /
函数已创建。
SQL> select f_stu() from dual;
F_STU()
---------------------------------------------------------
ok
在正常情况下一但发出commit或是rollback语句就会把这之前的所有修改提交或回滚，
但是在oracle中提供了一种自治事务，在自治事务中发出的commit或rollback语句只会对自治事务内的更改起做用而不会对自治事务外的修改起做用。
可以通过以下例子来看到这一结果：
首先建立一个表Create table Msg (Msg varchar(50)) 用于存储消息
建立自治事务下的插入过程
Create or Replace procedure AutoNomouse_Insert as
pragma autonomouse_transcation;
begin
insert into Msg values('AutoNomouse Insert');
commit;
end;
注：pragma autonomouse_transcation 表示在这个过程中启动自治事务
建立正常情况下的插入过程
Create or Replace Procedure NonAutoNomouse_Insert as
begin
insert into Msg Values('NonAutonomouse Insert');
commit;
end;
下面在一个匿名块中分别调用上面的两个过程
begin
insert into Msg Values('This Main Info');
NonAutoNomouse_Insert;
rollback;
end
select * from Msg
Msg
__________________________________________________
This Main Info
NonAutonomouse Insert
可以看到这个事务在过程NonAutoNomouse_Insert中被提交了Rollback并不起做用。
下面调用自治事务的过程如下：
begin
insert into Msg Values('This Main Info');
AutoNomouse_Insert;
rollback;
end
select * from Msg
MSG
--------------------------------------------------------------------------
AutoNomouse Insert
可以看到在自治事务中只提交了自治事务内的更改，所以Rollback回滚了自治事务外的更改。
-----------还有以下几个需要注意的地方。
在匿名PL/SQL块中,只有顶级的匿名PL/SQL块可以被设为AT
2. 如果AT试图访问被MT控制的资源,可能有deadlock发生.
3. Package 不能被声明为AT,只有package所拥有的function和procedure 才能声明为AT
4. AT程序必须以commit 或rollback结尾,否则会产生Oracle错误ORA-06519: active autonomous transaction detected and rolled back