自治事务

自治事务（autonomous transaction）允许你创建一个“事务中的事务），它能独立于其父事务提交
或回滚。利用自治事务，可以挂起当前执行的事务，开始一个新事务，完成一些工作，然后提交或回滚，
所有这些都不影响当前执行事务的状态。自治事务提供了一种用PL/SQL 控制事务的新方法，可以用于：
顶层匿名块；
本地（过程中的过程）、独立或打包的函数和过程；
对象类型的方法；
数据库触发器。

通过例子来说明。

创建一个简单的表来保存消息：create table t ( msg varchar2(25) );

接下来创建两个过程，每个过程只是将其名字插入到消息表中，然后提交。不过，其中一个过程是正
常的过程，另一个编写为自治事务。我们将使用这些对象来显示在各种情况下哪些工作会在数据库中持久
保留（被提交）。
首先是AUTONOMOUS_INSERT 过程：

create or replace procedure Autonomous_Insert as
pragma autonomous_transaction;
begin
insert into t values ('Autonomous Insert');
commit;
end;

使用pragma AUTONOMOUS_TRANSACTION。这个指令告诉数据库：执行这个过程时要作为一
个新的自治事务来执行，而且独立于其父事务。
注意: pragma 是一个编译器指令，这是一种编辑器执行某种编译选项的方法。还有其他一些pragma。

以下是“正常”的NONAUTONOMOUS_INSERT 过程：

create or replace procedure NonAutonomous_Insert as
begin
insert into t values ('NonAutonomous Insert');
commit;
end;

下面来观察PL/SQL 代码匿名块中非自治（nonautonomous）事务的行为：

begin
insert into t values ('Anonymous Block');
NonAutonomous_Insert;
rollback;
end;

select * from t;
MSG
-------------------------
Anonymous Block
NonAutonomous Insert

可以看到，匿名块执行的工作（INSERT）由NONAUTONOMOUNS_INSERT 过程提交。两个数据行都已提交，
所以ROLLBACK 命令没有什么可以回滚。把这个过程与自治事务过程的行为加以比较：

delete from t; commit;

begin
insert into t values ( 'Anonymous Block' );
Autonomous_Insert;
rollback;
end;

select * from t;
MSG
-------------------------
Autonomous Insert

在此，只有自治事务中完成并已提交的工作会持久保留。匿名块中完成的INSERT 由第4 行的回滚语
句回滚。自治事务过程的COMMIT 对匿名块中开始的父事务没有影响。

如果在一个“正常”的过程中COMMIT，它不仅会持久保留自己的工作，也会使该会话中
未完成的工作成为永久性的。不过，如果在一个自治事务过程中完成COMMIT，只会让这个过程本身的工作
成为永久性的。

下面是一个小例子。先在表上放一个自治事务触发器，它能捕获一个审计跟踪记录，详细地指出谁
试图更新表，这个人什么时候想更新表，另外还会提供一个描述性消息指出这个人想要修改什么数据。这
个触发器的基本逻辑是：对于不向你直接或间接报告的员工，要防止更新这些员工记录的任何企图。
首先，从SCOTT 模式建立EMP 表的一个副本，以此作为本例使用的表：

create table emp
as
select * from scott.emp;

要创建一个AUDIT_TAB 表，在这个表中存储审计信息。注意，我们使用了列的DEFAULT 属性，从而
默认具有当前登录的用户名以及登记审计跟踪信息的当前日期/时间：

create table audit_tab(username varchar2(30) default user, timestamp date default sysdate, msg varchar2(4000));

创建一个EMP_AUDIT 触发器对EMP 表上的UPDATE 活动进行审计：

create or replace trigger EMP_AUDIT
before update on 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;

注意，这里使用了CONNECT BY 查询。这会根据当前用户分析整个（员工）层次结构。它会验证我们
试图更新的记录是某个下属员工的记录，即这个人会在某个层次上向我们报告。
关于这个触发器的要点，主要如下：
PRAGMA AUTONOMOUS_TRANSACTION 应用于触发器定义。整个触发器是一个“自治事务”，因
此它独立于父事务（即企图完成更新的事务）。
触发器在查询中从它保护的表（EMP 表） 中具体读取。如果这不是一个自治事务，它本身
在运行时就会导致一个变异表错误。自治事务使我们绕开了这个问题，它允许我们读取表，但
是也带来了一个缺点， 我们无法看到自己对表做的修改。在这种情况下需要特别小心，这个逻
辑必须仔细审查。如果我们完成的事务是对员工层次结构本身的一个更新会怎么样？我们不会
在触发器中看到这些修改，在评估触发器的正确性时也要把这考虑在内。
触发器提交。这在以前不可能的，触发器以前从来不能提交工作。这个触发器并不是提交
父事务的工作（实际触发器触发的工作，即更新员工记录），而只是提交了触发器所完成的工作
（审计记录）。
在此，我们建立了EMP 表，其中一个妥善的层次结构（EMPNO-MGR 递归关系）。另外还有一个AUDIT_TAB
表，要在其中记录修改信息的失败企图。我们的触发器可以保证这样一个规则：只有我们的经理或经理的
经理（依此类推）可以修改我们的记录。

update emp set sal = sal*10;
update emp set sal = sal*10
*
ERROR at line 1:
ORA-20001: Access Denied
ORA-06512: at "OPS$TKYTE.EMP_AUDIT", line 21
ORA-04088: error during execution of trigger 'OPS$TKYTE.EMP_AUDIT'

select * from audit_tab;
USERNAME TIMESTAMP MSG
--------- --------- ----------------------------------------
OPS$TKYTE 27-APR-05 Attempt to update 7369

触发器发现了情况，能防止UPDATE 发生，而与此同时，会为这个企图创建一个永久记录（注意它在
AUDIT_TAB 表的CREATE TABLE 语句上如何使用DEFAULT 关键字来自动插入USER 和SYSDATE 值）。接下来，
假设我们作为一个用户登录，想实际完成一个UPDATE，并做一些尝试：

update ops$tkyte.emp set sal = sal*1.05 where ename = 'ADAMS';
1 row updated.update ops$tkyte.emp set sal = sal*1.05 where ename = 'SCOTT';
update ops$tkyte.emp set sal = sal*1.05 where ename = 'SCOTT'
*
ERROR at line 1:
ORA-20001: Access Denied
ORA-06512: at "OPS$TKYTE.EMP_AUDIT", line 21
ORA-04088: error during execution of trigger 'OPS$TKYTE.EMP_AUDIT'

在演示表EMP 的默认安装中，员工ADAMS 是SCOTT 的下属，所以第一个UPDATE 成功。再看第二个
UPDATE，SCOTT 试图给自己加薪，但是由于SCOTT 不向SCOTT 报告（SCOTT 不是自己的下属），所以这个更
新失败了。再登录回到包括AUDIT_TAB 表的模式，可以看到以下结果：

select * from audit_tab;
USERNAME TIMESTAMP MSG
--------- --------- -------------------------------------
---
OPS$TKYTE 27-APR-05 Attempt to update 7369
SCOTT 27-APR-05 Attempt to update 7788