PolarDB:PRAGMA AUTONOMOUS_TRANSACTION

シナリオ

• シナリオ1a: 最終COMMITのみの自律トランザクションはありません

  最初のシナリオは、3行を挿入する方法を示しています。 トランザクションの最初のBEGINステートメントの後に最初の行を挿入し、開始したトランザクションの匿名ブロックに2番目の行を挿入します。 次に、匿名ブロックで実行されるストアドプロシージャの3行目を挿入します。

  次に、ストアドプロシージャの例を示します。

  作成または変更手順insert_dept_70 IS
  開始
      部門の値に挿入する (70、「マーケティング」、「ロサンゼルス」);
  エンド; 

  次の例は、PostgreSQLセッションを示しています。

  BEGIN;
  部門の値に挿入します (50、'HR' 、'DENVER');
  開始
      部門の値に挿入する (60、「金融」、「シカゴ」);
      insert_dept_70;
  エンド;
  コミット; 

  最後のコミット後、3行すべてが挿入されます。

  SELECT * からのdept順序1;
  
   deptno | dname | loc
  -------- ------------ ----------------------
       10 | ACCOUNTING | NEW YORK
       20 | RESEARCH   | DALLAS
       30 | SALES      | CHICAGO
       40 | OPERATIONS | BOSTON
       50 | HR | デンバー
       60 | ファイナンス | シカゴ
       70 | マーケティング | ロサンゼルス
  (7行) 

• シナリオ1b: 自律的なトランザクションはありませんが、最終的なROLLBACK

  次のシナリオは、すべての挿入が3つすべての挿入のロールバックをもたらした後の最終的なROLLBACKステートメントを示しています。

  BEGIN;
  部門の値に挿入します (50、'HR' 、'DENVER');
  開始
      部門の値に挿入する (60、「金融」、「シカゴ」);
      insert_dept_70;
  エンド;
  ロールバック;
  
  SELECT * からのdept順序1;
  
   deptno | dname | loc
  ------- ------------ ------------------
       10 | ACCOUNTING | NEW YORK
       20 | RESEARCH   | DALLAS
       30 | SALES      | CHICAGO
       40 | 操作 | ボストン
  (4行) 

• シナリオ1c: 自律的なトランザクションはありませんが、匿名ブロックROLLBACK

  匿名ブロックの最後にあるROLLBACKステートメントも、3つの挿入すべてがロールバックされます。

  BEGIN;
  部門の値に挿入します (50、'HR' 、'DENVER');
  開始
      部門の値に挿入する (60、「金融」、「シカゴ」);
      insert_dept_70;
      ロールバック;
  エンド;
  COMMIT;
  
  SELECT * からのdept順序1;
  
   deptno | dname | loc
  ------- ------------ ------------------
       10 | ACCOUNTING | NEW YORK
       20 | RESEARCH   | DALLAS
       30 | SALES      | CHICAGO
       40 | 操作 | ボストン
  (4行) 

  次のシナリオでは、別のステートメントでPRAGMA AUTONOMOUS_TRANSACTIONを使用して自律トランザクションを有効にした場合の効果を示します。

• シナリオ2a: COMMITを使用した匿名ブロックの自律トランザクション

  ストアドプロシージャは変更されません。

  作成または変更手順insert_dept_70 IS
  開始
      部門の値に挿入する (70、「マーケティング」、「ロサンゼルス」);
  エンド; 

  PRAGMA AUTONOMOUS_TRANSACTIONは、匿名ブロックの最後にCOMMITステートメントを使用して匿名ブロックに提供されます。

  BEGIN;
  部門の値に挿入します (50、'HR' 、'DENVER');
  DECLARE
      プラハAUTONOMOUS_TRANSACTION;
  開始
      部門の値に挿入する (60、「金融」、「シカゴ」);
      insert_dept_70;
      COMMIT;
  エンド;
  ロールバック; 

  トランザクションの最後のROLLBACK文が実行された後、トランザクションの最初の挿入のみがロールバックされます。 PRAGMA AUTONOMOUS_TRANSACTIONを使用して匿名ブロックに挿入された他の2行がコミットされました。

  SELECT * からのdept順序1;
  
   deptno | dname | loc
  -------- ------------ ----------------------
       10 | ACCOUNTING | NEW YORK
       20 | RESEARCH   | DALLAS
       30 | SALES      | CHICAGO
       40 | OPERATIONS | BOSTON
       60 | ファイナンス | シカゴ
       70 | マーケティング | ロサンゼルス
  (6行) 

• シナリオ2b: ROLLBACKのストアドプロシージャを含むが、自律トランザクションプロシージャではないCOMMITの自律トランザクション匿名ブロック

  ROLLBACKステートメントは、ストアドプロシージャの最後にあります。 ただし、PRAGMA ANONYMOUS_TRANSACTIONはこのストアドプロシージャに含まれません。

  作成または変更手順insert_dept_70 IS
  開始
      部門の値に挿入する (70、「マーケティング」、「ロサンゼルス」);
      ロールバック;
  エンド; 

  ストアドプロシージャのロールバックにより、匿名ブロックの最後のCOMMITステートメントの前に匿名ブロック (deptno 60および70) に挿入された2行が削除されます。

  BEGIN;
  部門の値に挿入します (50、'HR' 、'DENVER');
  DECLARE
      プラハAUTONOMOUS_TRANSACTION;
  開始
      部門の値に挿入する (60、「金融」、「シカゴ」);
      insert_dept_70;
      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 | デンバー
  (5行) 

• シナリオ2c: ROLLBACKを使用したストアドプロシージャを含むCOMMITを使用した自律トランザクション匿名ブロック。

  最後にROLLBACK文を含むストアドプロシージャには、PRAGMA ANONYMOUS_TRANSACTIONも含まれます。 これにより、ストアドプロシージャ内のROLLBACKステートメントの効果が分離されます。

  作成または変更手順insert_dept_70 IS
      プラハAUTONOMOUS_TRANSACTION;
  開始
      部門の値に挿入する (70、「マーケティング」、「ロサンゼルス」);
      ロールバック;
  エンド; 

  ストアドプロシージャのロールバックは、ストアドプロシージャによって挿入された行を削除します。 ただし、匿名ブロックに挿入された他の行は削除されません。

  BEGIN;
  部門の値に挿入します (50、'HR' 、'DENVER');
  DECLARE
      プラハAUTONOMOUS_TRANSACTION;
  開始
      部門の値に挿入する (60、「金融」、「シカゴ」);
      insert_dept_70;
      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 | デンバー
       60 | ファイナンス | シカゴ
  (6行) 

  次のセクションでは、他の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は宣言に含まれます。

トリガーの作成または置き換えemp_audit_trig
    empの挿入または更新または削除後
DECLARE
    プラハAUTONOMOUS_TRANSACTION;
    v_action VARCHAR2(20);
開始
    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);
エンド; 

次の2つの行が、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テーブルの2つの新しい行とempauditlogテーブルの2つのエントリを示しています。

SELECT * FROM emp WHERE empno > 9000;

 empno | ename | job | mgr | hiredate | sal | comm | deptno
------ ------- --------- ----------- -------------------- -------------------------
  9001 | スミス | アナリスト | 7782 | 23-AUG-18 07:12:27 | | | | 10
  9002 | ジョーンズ | クレーク | 7782 | 23-AUG-18 07:12:27 | | | | 10
(2行)

SELECT TO_CHAR(AUDIT_DATE、「DD MON-YY HH24:MI:SS」) を「監査日」として、
    audit_user, audit_desc FROM empauditlog ORDER BY 1 ASC;

     監査日 | audit_user | audit_desc
-------------------- -------------- -------------------
 23-AUG-18 07:12:27 | polardb | 従業員を追加
 23-AUG-18 07:12:27 | polardb | 従業員を追加
(2行) 

ただし、ROLLBACK文はこのセッションで実行されます。 新しく挿入された2つの行はempテーブルには存在しませんが、前の2つのエントリはempauditlogテーブルにはまだ存在します。 これは、トリガーが暗黙的に変更をコミットし、PRAGMA AUTONOMOUS_TRANSACTIONが呼び出しトランザクションのロールバックに関係なく変更をコミットするためです。

ROLLBACK;

empno > 9000を選択します。

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

SELECT TO_CHAR(AUDIT_DATE、「DD MON-YY HH24:MI:SS」) を「監査日」として、
    audit_user, audit_desc FROM empauditlog ORDER BY 1 ASC;

     監査日 | audit_user | audit_desc
-------------------- -------------- -------------------
 23-AUG-18 07:12:27 | polardb | 従業員を追加
 23-AUG-18 07:12:27 | polardb | 従業員を追加
(2行) 

Autonomousトランザクションオブジェクト型メソッド

次の例は、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) 、
    メンバー手順insert_dept
);

CREATEまたはREPLACE TYPE BODY insert_dept_typ AS
    メンバー手順insert_dept
    IS
        プラハAUTONOMOUS_TRANSACTION;
    BEGIN
        部門の値に挿入 (SELF.de ptno、SELF.dname、SELF.loc);
        COMMIT;
    エンド;
エンド; 

次の匿名ブロックでは、システムはdeptテーブルに行を挿入し、insert_deptメソッドを呼び出してから、匿名ブロックの最後でROLLBACKステートメントを実行します。

BEGIN;
DECLARE
    v_dept INSERT_DEPT_TYP :=
                      insert_dept_typ(60、'FINANCE' 、'CHICAGO');
開始
    部門の値に挿入します (50、'HR' 、'DENVER');
    v_dept.insert_dept;
    ロールバック;
エンド; 

insert_deptは自律トランザクションとして宣言されています。 したがって、部門60の行はテーブルに残ります。 しかし、ロールバック動作は、部門50の挿入された行を削除する。

SELECT * からのdept順序1;

 deptno | dname | loc
------- ------------ ------------------
     10 | ACCOUNTING | NEW YORK
     20 | RESEARCH   | DALLAS
     30 | SALES      | CHICAGO
     40 | OPERATIONS | BOSTON
     60 | ファイナンス | シカゴ
(5行)