【PL/SQL】トランザクション・コミット・ロールバックについて

ここではORACLE社が提供するPL/SQL言語のトランザクション・コミット・ロールバックについて紹介しています。

トランザクションとは

PL/SQLプログラムの一連の処理のまとまりをトランザクションと呼びます。

PL/SQLの処理とは、1つ以上のSQL文のことです。

PL/SQLでは、トランザクションの単位でデータベース操作の確定（コミット）や取消（ロールバック）が行われます。

コミット（COMMIT）

トランザクション単位にデータベース操作を確定させることをコミット（COMMIT）と呼びます。

例えば、あるテーブルに50件のレコードをINSERTした後に確定する場合はコミットを実行します。

コミットは次の構文で実行できます。

commit;

次の例ではINSERT文を実行した後にコミットして確定しています。

テーブルをSELECTするとレコードが登録されていることを確認できます。

SQL> BEGIN
  2      EXECUTE IMMEDIATE 'INSERT INTO tab1(emp_id) VALUES(' || CHR(39) || '00001' || CHR(39) || ')';
  3      COMMIT;
  4  END;
  5  /

PL/SQLプロシージャが正常に完了しました。

SQL> select * from tab1;

EMP_ID     EMP_NAME             DEPT              AGE
---------- -------------------- ---------- ----------
00001

分割コミット

分割コミットとは、1件ごとにコミットするのではなく、ある程度まとまった件数ごとにSQL文をコミットしていくことです。

特に大量のINSERT文やUPDATE文、DELETE文を実行する場合に行います。

次の例では、データを合計で100件登録していますが、INSERT文を10件実行するごとにコミットを入れています。

BEGIN
    FOR i IN 1..100 LOOP
        EXECUTE IMMEDIATE 'INSERT INTO tab1(emp_id) VALUES(' || CHR(39) || LPAD(i,5,0) || CHR(39) || ')';
        IF MOD(i,10) = 0 THEN
            COMMIT;
        END IF;
    END LOOP;
END;
/

実行すると次のように100件が登録されていることが確認できます。

SQL> BEGIN
  2      FOR i IN 1..100 LOOP
  3          EXECUTE IMMEDIATE 'INSERT INTO tab1(emp_id) VALUES(' || CHR(39) || LPAD(i,5,0) || CHR(39) || ')';
  4          IF MOD(i,10) = 0 THEN
  5              COMMIT;
  6          END IF;
  7      END LOOP;
  8  END;
  9  /

PL/SQLプロシージャが正常に完了しました。

SQL> select COUNT(1),MIN(emp_id),MAX(emp_id) from tab1;

  COUNT(1) MIN(EMP_ID MAX(EMP_ID
---------- ---------- ----------
       100 00001      00100

ロールバック（ROLLBACK）

トランザクション単位にデータベース操作を取消させることをロールバック（ROLLBACK）と呼びます。

例えば、あるテーブルに50件のレコードをINSERTした後に、取消する場合はロールバックを実行します。

ただし、一度コミットした操作は取り消しすることが出来ないので注意が必要がです。

ロールバックは次の構文で実行できます。

ROLLBACK;

次の例ではINSERT文を実行した後にロールバックして取消しています。

テーブルをSELECTするとレコードが登録されていないことを確認できます。

SQL> BEGIN
  2      EXECUTE IMMEDIATE 'INSERT INTO tab1(emp_id) VALUES(' || CHR(39) || '00001' || CHR(39) || ')';
  3      ROLLBACK;
  4  END;
  5  /

PL/SQLプロシージャが正常に完了しました。

SQL> select * from tab1;

レコードが選択されませんでした。

自律型トランザクション

自律型トランザクション（PRAGMA AUTONOMOUS_TRANSACTION）とは、呼び出し元のトランザクションとは独立したトランザクションです。

自律型トランザクションを使う場合は、宣言部でPRAGMA AUTONOMOUS_TRANSACTIONを宣言します。

自律型トランザクションのサンプル

まず、自律型トランザクションを宣言したプロシージャ3を作成します。

PRO3はtab1テーブルに1件レコードを登録します。

CREATE OR REPLACE PROCEDURE PRO3
IS
      PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
      INSERT INTO tab1 VALUES('00001','Suzuki','D0001',28);
      COMMIT;
END;
/

次にPRO４でPRO3を呼び出します。

このとき、EXECUTEで実行したINSERT文は構文エラーとなり例外処理部に遷移します。

例外処理部でPRO３を呼び出した後、ROLLBACKを実行します。

CREATE OR REPLACE PROCEDURE PRO4
IS
BEGIN
      EXECUTE IMMEDIATE 'INSERT INTO tab1 VALUES(' || CHR(39) || '00002' || CHR(39) || ')';
      COMMIT;
EXCEPTION WHEN OTHERS THEN
      PRO3;
      ROLLBACK;
END;
/

実行します。
PRO3のトランザクションはロールバックされることなく実行され、コミットされていることが分かります。

SQL> EXECUTE PRO4;

PL/SQLプロシージャが正常に完了しました。

SQL> select * from tab1;

EMP_ID     EMP_NAME             DEPT              AGE
---------- -------------------- ---------- ----------
00001      Suzuki               D0001              28