oracle 自治事务 函数,oracle数据库自治事务的学习笔记

oracle自治事务是指的存储过程和函数可以自己处理内部事务不受外部事务的影响，用pragma autonomous_transaction来声明。要创建一个自治事务，必须在匿名块的最高层或者存储过程、函数、数据包或触发器的定义部分中，使用PL/SQL中的PRAGMA AUTONOMOUS_TRANSACTION语句。

以下是使用自治事务的几种情况：

(1)Declaring Autonomous Function in Package

CREATE OR REPLACE PACKAGE emp_actions AS

-- package specification

FUNCTION raise_salary(emp_id    NUMBER,

sal_raise NUMBER) RETURN NUMBER;

END emp_actions;

/

CREATE OR REPLACE PACKAGE BODY emp_actions AS

-- package body

-- code for function raise_salary

FUNCTION raise_salary(emp_id    NUMBER,

sal_raise NUMBER) RETURN NUMBER IS

PRAGMA AUTONOMOUS_TRANSACTION;

new_sal NUMBER(8,2);

BEGIN

UPDATE employees

SET salary = salary + sal_raise

WHERE employee_id = emp_id;

COMMIT;

SELECT salary

INTO new_sal

FROM employees

WHERE employee_id = emp_id;

RETURN new_sal;

END raise_salary;

END emp_actions;

/

(2)Declaring Autonomous Standalone Procedure

CREATE OR REPLACE PROCEDURE lower_salary(emp_id NUMBER,

amount NUMBER) AS

PRAGMA AUTONOMOUS_TRANSACTION;

BEGIN

UPDATE employees

SET salary = salary - amount

WHERE employee_id = emp_id;

COMMIT;

END lower_salary;

/

(3)Declaring Autonomous PL/SQL Block

DECLARE

PRAGMA AUTONOMOUS_TRANSACTION;

emp_id NUMBER(6) := 200;

amount NUMBER(6, 2) := 200;

BEGIN

UPDATE employees

SET salary = salary - amount

WHERE employee_id = emp_id;

COMMIT;

END;

/

(4)Declaring Autonomous in Trigger

CREATE OR REPLACE TRIGGER log_sal

BEFORE UPDATE OF salary ON emp

FOR EACH ROW

DECLARE

PRAGMA AUTONOMOUS_TRANSACTION;

BEGIN

INSERT INTO log

(log_id, up_date, new_sal, old_sal)

VALUES

(:old.employee_id, SYSDATE, :new.salary, :old.salary);

COMMIT;

END;

自治事务可以嵌套，嵌套深度等只受init.ora参数transactions(同时并发的事务数，缺省为sessions的1.1倍)制约。因为自治事务是与主事务(简称MT)相分离的，所以它不能检测到被修改过的行的当前状态。这就好像在主事务提交之前，它们一直处于单独的会话里，对自治事务来说，它们是不可用的。然而，反过来情况就不同了：主事务能够检测到已经执行过的自治事务的结果。

如果AT试图访问被MT控制的资源,可能有deadlock发生，Package不能被声明为AT，只有package所拥有的function和procedure 才能声明为AT。主事务与自治事务是完全不同的事务，因此无法共享锁等。结束一个自治事务必须提交一个commit、rollback或执行ddl，否则会产生Oracle错误ORA-06519: active autonomous transaction detected and rolled back 。保存点无法在自治事务中回滚到父事务中的一个保存点，只能在内部使用保存点。

可能遇到的错误：

ora-06519 – 检查到活动自治事务，回滚——退出自治事务时没有提交、回滚或ddl操作

ora-14450 – 试图访问正在使用的事务级临时表

ora-00060 – 等待资源时检查到死锁