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

Love The Way You Lie 2022-10-21 11:41 320阅读 0赞

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 – 等待资源时检查到死锁