PRAGMA AUTONOMOUS_TRANSACTION_事务控制_存储过程语言_兼容Oracle数据库开发指南_云数据库PolarDB_阿里云技术文档

通过在 SPL 块的声明部分中指定以下指令，可将 SPL 程序声明为自治事务：

PRAGMA AUTONOMOUS_TRANSACTION;

自治事务是由调用程序启动的独立事务。自治事务中 SQL 命令的提交或回滚对调用程序的任何事务中的提交或回滚都没有影响。调用程序中的提交或回滚对自治事务中 SQL 命令的提交或回滚也没有影响。

以下 SPL 程序可包含 PRAGMA AUTONOMOUS_TRANSACTION：

独立的存储过程和函数。
匿名块。
包中声明为子程序的存储过程和函数以及其他调用存储过程、函数和匿名块。
触发器。
对象类型方法。

下面是与自治事务有关的问题和限制：

每个自治事务只要在进行中，就会消耗一个连接槽。在某些情况下，这可能意味着应增大 postgresql.conf 文件中的 max_connections 参数。
在大多数方面，自治事务的行为就像是一个完全独立的会话，但 GUC（即通过 SET 建立的设置）是一个有意制造的例外。自治事务吸收周围的值，并可以将它们提交的值传播到外部事务。
自治事务可以嵌套，但在单个会话中自治事务的嵌套级别限制为 16 级。
自治事务中不支持并行查询。
自治事务的 POLARDB for Oracle 实现与 Oracle 数据库不完全兼容，因为如果 SPL 块末尾有未提交的事务，则 POLARDB for Oracle 自治事务不会产生错误。

以下一组示例阐释了自治事务的用法。第一组场景显示了没有自治事务时的默认行为。

在每个场景之前，dept 表重置为以下初始值：

SELECT * FROM dept;

 deptno |   dname    |   loc
--------+------------+----------
     10 | ACCOUNTING | NEW YORK
     20 | RESEARCH   | DALLAS
     30 | SALES      | CHICAGO
     40 | OPERATIONS | BOSTON
(4 rows)

场景 1a – 没有自治事务，只有最终 COMMIT

第一组场景显示了如何插入三行，首先就从事务的初始 BEGIN 命令之后开始插入第一行，然后从起始事务的匿名块插入第二行，最后从匿名块内执行的存储过程插入第三行。

该存储过程如下：

CREATE OR REPLACE PROCEDURE insert_dept_70 IS
BEGIN
    INSERT INTO dept VALUES (70,'MARKETING','LOS ANGELES');
END;

PSQL 会话如下：

BEGIN;
INSERT INTO dept VALUES (50,'HR','DENVER');
BEGIN
    INSERT INTO dept VALUES (60,'FINANCE','CHICAGO');
    insert_dept_70;
END;
COMMIT;

在最后提交后，将插入所有三行：

SELECT * FROM dept ORDER BY 1;

 deptno |   dname    |     loc
--------+------------+-------------
     10 | ACCOUNTING | NEW YORK
     20 | RESEARCH   | DALLAS
     30 | SALES      | CHICAGO
     40 | OPERATIONS | BOSTON
     50 | HR         | DENVER
     60 | FINANCE    | CHICAGO
     70 | MARKETING  | LOS ANGELES
(7 rows)

场景 1b – 没有自治事务，但有最终 ROLLBACK

下一个场景显示，所有插入之后的最后一个 ROLLBACK 命令将导致所有三个插入的回滚：

BEGIN;
INSERT INTO dept VALUES (50,'HR','DENVER');
BEGIN
    INSERT INTO dept VALUES (60,'FINANCE','CHICAGO');
    insert_dept_70;
END;
ROLLBACK;

SELECT * FROM dept ORDER BY 1;

 deptno |   dname    |   loc
--------+------------+----------
     10 | ACCOUNTING | NEW YORK
     20 | RESEARCH   | DALLAS
     30 | SALES      | CHICAGO
     40 | OPERATIONS | BOSTON
(4 rows)

场景 1c – 没有自治事务，但有匿名块 ROLLBACK

匿名块结尾给出的 ROLLBACK 命令也消除了所有以前的三个插入：

BEGIN;
INSERT INTO dept VALUES (50,'HR','DENVER');
BEGIN
    INSERT INTO dept VALUES (60,'FINANCE','CHICAGO');
    insert_dept_70;
    ROLLBACK;
END;
COMMIT;

SELECT * FROM dept ORDER BY 1;

 deptno |   dname    |   loc
--------+------------+----------
     10 | ACCOUNTING | NEW YORK
     20 | RESEARCH   | DALLAS
     30 | SALES      | CHICAGO
     40 | OPERATIONS | BOSTON
(4 rows)

下一组场景显示了在不同位置使用 PRAGMA AUTONOMOUS_TRANSACTION 自治事务的效果。

场景 2a – 带有 COMMIT 的匿名块的自治事务

存储过程保持最初创建的样子：

CREATE OR REPLACE PROCEDURE insert_dept_70 IS
BEGIN
    INSERT INTO dept VALUES (70,'MARKETING','LOS ANGELES');
END;

现在，PRAGMA AUTONOMOUS_TRANSACTION 通过匿名块给出，并且匿名块末尾给出 COMMIT 命令。

BEGIN;
INSERT INTO dept VALUES (50,'HR','DENVER');
DECLARE
    PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
    INSERT INTO dept VALUES (60,'FINANCE','CHICAGO');
    insert_dept_70;
    COMMIT;
END;
ROLLBACK;

在事务结束时执行 ROLLBACK 后，只丢弃了事务开始时的第一行插入。带有 PRAGMA AUTONOMOUS_TRANSACTION 的匿名块中的另两行插入已独立提交。

SELECT * FROM dept ORDER BY 1;

 deptno |   dname    |     loc
--------+------------+-------------
     10 | ACCOUNTING | NEW YORK
     20 | RESEARCH   | DALLAS
     30 | SALES      | CHICAGO
     40 | OPERATIONS | BOSTON
     60 | FINANCE    | CHICAGO
     70 | MARKETING  | LOS ANGELES
(6 rows)

场景 2b – 带有 COMMIT 的自治事务匿名块包含带有 ROLLBACK 的存储过程，而不是自治事务过程

现在，存储过程在末尾具有 ROLLBACK 命令。但是，您会看到 PRAGMA ANONYMOUS_TRANSACTION 未包含在此存储过程中。

CREATE OR REPLACE PROCEDURE insert_dept_70 IS
BEGIN
    INSERT INTO dept VALUES (70,'MARKETING','LOS ANGELES');
    ROLLBACK;
END;

现在，该存储过程中的回滚会在匿名块中的最终 COMMIT 命令之前删除匿名块中插入的两行（deptno 60 和 70）。

BEGIN;
INSERT INTO dept VALUES (50,'HR','DENVER');
DECLARE
    PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
    INSERT INTO dept VALUES (60,'FINANCE','CHICAGO');
    insert_dept_70;
    COMMIT;
END;
COMMIT;

在事务结束时进行最终提交之后，插入的唯一一行是事务开始时插入的第一行。由于匿名块是自治事务，因此封闭存储过程中的回滚对执行匿名块之前发生的插入没有影响。

SELECT * FROM dept ORDER by 1;

 deptno |   dname    |   loc
--------+------------+----------
     10 | ACCOUNTING | NEW YORK
     20 | RESEARCH   | DALLAS
     30 | SALES      | CHICAGO
     40 | OPERATIONS | BOSTON
     50 | HR         | DENVER
(5 rows)

场景 2c – 带有 COMMIT 的自治事务匿名块包含带 ROLLBACK 的存储过程，该过程也是自治事务过程

现在，在末尾具有 ROLLBACK 命令的存储过程也包含 PRAGMA ANONYMOUS_TRANSACTION。这将隔离该存储过程中 ROLLBACK 命令的效果。

CREATE OR REPLACE PROCEDURE insert_dept_70 IS
    PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
    INSERT INTO dept VALUES (70,'MARKETING','LOS ANGELES');
    ROLLBACK;
END;

现在，该存储过程中的回滚会删除由该过程插入的行，而不是在匿名块中插入的其他行。

BEGIN;
INSERT INTO dept VALUES (50,'HR','DENVER');
DECLARE
    PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
    INSERT INTO dept VALUES (60,'FINANCE','CHICAGO');
    insert_dept_70;
    COMMIT;
END;
COMMIT;

在事务结束时进行最终提交后，插入的行是从事务开始时插入的第一行，以及在匿名块开始时插入的行。回滚的唯一插入是该存储过程中的插入。

SELECT * FROM dept ORDER by 1;

 deptno |   dname    |   loc
--------+------------+----------
     10 | ACCOUNTING | NEW YORK
     20 | RESEARCH   | DALLAS
     30 | SALES      | CHICAGO
     40 | OPERATIONS | BOSTON
     50 | HR         | DENVER
     60 | FINANCE    | CHICAGO
(6 rows)

现在，以下各节显示了一系列其他 SPL 程序类型中的 PRAGMA AUTONOMOUS_TRANSACTION 的示例。

自治事务触发器

以下示例显示了使用 PRAGMA AUTONOMOUS_TRANSACTION 声明触发器的效果。

下表是为了记录对 emp 表的更改而创建的：

CREATE TABLE empauditlog (
    audit_date      DATE,
    audit_user      VARCHAR2(20),
    audit_desc      VARCHAR2(20)
);

附加到 emp 表并将这些更改插入 empauditlog 表的触发器如下：您会看到，在声明部分中包含了 PRAGMA AUTONOMOUS_TRANSACTION。

CREATE OR REPLACE TRIGGER emp_audit_trig
    AFTER INSERT OR UPDATE OR DELETE ON emp
DECLARE
    PRAGMA AUTONOMOUS_TRANSACTION;
    v_action        VARCHAR2(20);
BEGIN
    IF INSERTING THEN
        v_action := 'Added employee(s)';
    ELSIF UPDATING THEN
        v_action := 'Updated employee(s)';
    ELSIF DELETING THEN
        v_action := 'Deleted employee(s)';
    END IF;
    INSERT INTO empauditlog VALUES (SYSDATE, USER,
        v_action);
END;

在 BEGIN 命令启动的事务中执行了以下两个插入到 emp 表的操作。

BEGIN;
INSERT INTO emp VALUES (9001,'SMITH','ANALYST',7782,SYSDATE,NULL,NULL,10);
INSERT INTO emp VALUES (9002,'JONES','CLERK',7782,SYSDATE,NULL,NULL,10);

下面显示了 emp 表中的两个新行以及 empauditlog 表中的两个条目：

SELECT * FROM emp WHERE empno > 9000;

 empno | ename |   job   | mgr  |      hiredate      | sal | comm | deptno
-------+-------+---------+------+--------------------+-----+------+--------
  9001 | SMITH | ANALYST | 7782 | 23-AUG-18 07:12:27 |     |      |     10
  9002 | JONES | CLERK   | 7782 | 23-AUG-18 07:12:27 |     |      |     10
(2 rows)

SELECT TO_CHAR(AUDIT_DATE,'DD-MON-YY HH24:MI:SS') AS "audit date",
    audit_user, audit_desc FROM empauditlog ORDER BY 1 ASC;

     audit date     |  audit_user  |    audit_desc
--------------------+--------------+-------------------
 23-AUG-18 07:12:27 | enterprisedb | Added employee(s)
 23-AUG-18 07:12:27 | enterprisedb | Added employee(s)
(2 rows)

但随后在此会话期间给出了 ROLLBACK 命令。emp 表不再包含这两行，而 empauditlog 表仍包含其两个条目，这是因为触发器隐式执行了提交，并且 PRAGMA AUTONOMOUS_TRANSACTION 提交这些更改的操作独立于调用事务中给出的回滚。

ROLLBACK;

SELECT * FROM emp WHERE empno > 9000;

 empno | ename | job | mgr | hiredate | sal | comm | deptno
-------+-------+-----+-----+----------+-----+------+--------
(0 rows)

SELECT TO_CHAR(AUDIT_DATE,'DD-MON-YY HH24:MI:SS') AS "audit date",
    audit_user, audit_desc FROM empauditlog ORDER BY 1 ASC;

     audit date     |  audit_user  |    audit_desc
--------------------+--------------+-------------------
 23-AUG-18 07:12:27 | enterprisedb | Added employee(s)
 23-AUG-18 07:12:27 | enterprisedb | Added employee(s)
(2 rows)

自治事务对象类型方法

以下示例显示了使用 PRAGMA AUTONOMOUS_TRANSACTION 声明对象方法的效果。

将创建以下对象类型和对象类型主体。对象类型主体中的成员存储过程包含声明部分中的 PRAGMA AUTONOMOUS_TRANSACTION 以及位于存储过程结尾的 COMMIT。

CREATE OR REPLACE TYPE insert_dept_typ AS OBJECT (
    deptno          NUMBER(2),
    dname           VARCHAR2(14),
    loc             VARCHAR2(13),
    MEMBER PROCEDURE insert_dept
);

CREATE OR REPLACE TYPE BODY insert_dept_typ AS
    MEMBER PROCEDURE insert_dept
    IS
        PRAGMA AUTONOMOUS_TRANSACTION;
    BEGIN
        INSERT INTO dept VALUES (SELF.deptno,SELF.dname,SELF.loc);
        COMMIT;
    END;
END;

在以下匿名块中，将执行一个插入到 dept 表的操作，然后调用对象的 insert_dept 方法，最后在匿名块中执行 ROLLBACK 命令。

BEGIN;
DECLARE
    v_dept          INSERT_DEPT_TYP :=
                      insert_dept_typ(60,'FINANCE','CHICAGO');
BEGIN
    INSERT INTO dept VALUES (50,'HR','DENVER');
    v_dept.insert_dept;
    ROLLBACK;
END;

由于 insert_dept 已声明为自治事务，因此其插入的部门编号 60 仍位于表中，但回滚删除了插入的部门 50。

SELECT * FROM dept ORDER BY 1;

 deptno |   dname    |   loc
--------+------------+----------
     10 | ACCOUNTING | NEW YORK
     20 | RESEARCH   | DALLAS
     30 | SALES      | CHICAGO
     40 | OPERATIONS | BOSTON
     60 | FINANCE    | CHICAGO
(5 rows)

PRAGMA AUTONOMOUS_TRANSACTION_事务控制_存储过程语言_兼容Oracle数据库开发指南_云数据库PolarDB

PRAGMA AUTONOMOUS_TRANSACTION