💡
[Oracle PL/SQL] テーブルのスナップショットを作成するプロシージャを考えてみる

2024/10/05に公開
Oracle
snapshot
plsql
tech
コードのクッソ長いプロシージャの調査をしていて困るのが、更新テーブルの途中経過が確認しづらいことです。特に一時表（TEMPORARY TABLE）が使われていると困難を極めます。
CREATE TABLE AS SELECTでデータ退避する方法もありますが、例外が発生してロールバックされると中身が消えてしまい使えません。SAVEPOINTを使う方法もありますが、私にはしっくりきませんでした。
Oracleが提供するスナップショット機能を使う方法もあると思いますが、もっとお手軽にテーブルを複製したかったのでプロシージャを作成してみることにしました。

 コードそして出来上がったのが↓こちらです。

自律型トランザクションと動的SQL、バルクフェッチを使って実現しています。
create or replace PROCEDURE SP_TABLE_SNAPSHOT
/**
  * プロシージャ: SP_TABLE_SNAPSHOT
  *
  * 指定されたテーブル名のスナップショット（複製）を作成します。
  * 呼出後にロールバックが実行されてもデータは消えません。
  *
  * 複製するレコード件数が多いほど実行速度は低下します。
  * バルクフェッチ件数を増やすか、行フィルターで必要な分だけ複製してください。
  *
  * この機能を使用するには、CREATE ANY TABLE権限が必要です。
  * GRANT CREATE ANY TABLE TO <ユーザ名>;
  *
  * 使い方:
  * SP_TABLE_SNAPSHOT('T_SAMPLE');
  * SP_TABLE_SNAPSHOT('T_SAMPLE', 'AGE >= 20 AND LAST_NAME = ''太郎'' ');
  * SP_TABLE_SNAPSHOT('T_SAMPLE', ﻿snapshot_name => 'T_SAMPLE$1005', bluk_size => 200);
  *
  * @param table_name テーブル名
  * @param row_filter 行フィルター
  * @param snapshot_name スナップショット名（初期値: テーブル名$MMDDHH24MISS）
  * @param bluk_size バルクフェッチ件数（初期値: 100件）
  * @param uncheck_dollar_sign スナップショット名に「$」を含めたくない場合は 1 を指定
  * @param max_identifier_length 識別子名の最大長（初期値: 128文字）
  */
(
    table_name              IN VARCHAR2
,   row_filter              IN VARCHAR2 DEFAULT NULL
,   snapshot_name           IN VARCHAR2 DEFAULT NULL
,   bluk_size               IN INTEGER  DEFAULT NULL
,   uncheck_dollar_sign     IN INTEGER  DEFAULT NULL
,   max_identifier_length   IN INTEGER  DEFAULT NULL
)
IS
    wk_table_name       VARCHAR2(128);
    wk_snapshot_name    VARCHAR2(128);
    wk_sql              VARCHAR2(2000);

    -- テーブルの存在チェック
    FUNCTION is_table_exists
    (
        target_name     IN VARCHAR2
    )
    RETURN BOOLEAN
    IS
        wk_table_cout   INTEGER;
    BEGIN
        SELECT COUNT(*)
        INTO wk_table_cout
        FROM USER_TABLES
        WHERE TABLE_NAME = target_name;
        IF wk_table_cout > 0 THEN
            RETURN TRUE;
        ELSE
            RETURN FALSE;
        END IF;
    END;

    -- 複製テーブルを作成(データなし)
    PROCEDURE create_snapshot_table
    IS
        -- DDL文はメイン・トランザクションと別で実行する
        PRAGMA AUTONOMOUS_TRANSACTION;
    BEGIN
        IF is_table_exists(wk_snapshot_name) = TRUE THEN
            EXECUTE IMMEDIATE 'DROP TABLE '|| wk_snapshot_name ||' CASCADE CONSTRAINTS PURGE';
        END IF;
        EXECUTE IMMEDIATE 'CREATE TABLE '|| wk_snapshot_name ||' AS SELECT * FROM '|| wk_table_name ||' WHERE 1 <> 1';
        IF row_filter IS NOT NULL THEN
            EXECUTE IMMEDIATE 'COMMENT ON TABLE '|| wk_snapshot_name ||' IS '''|| REPLACE(row_filter, '''', '''''') ||'''';
        END IF;
    END;

    -- 複製テーブルにデータコピー
    PROCEDURE data_copy
    IS
        BEGIN
            wk_sql := 'DECLARE';
            wk_sql := wk_sql ||'  CURSOR cur IS SELECT * FROM '|| wk_table_name;
            IF row_filter IS NOT NULL THEN
                wk_sql := wk_sql ||' WHERE '|| row_filter;
            END IF;
            wk_sql := wk_sql ||'  ;';
            wk_sql := wk_sql ||'  TYPE cur_tbl IS TABLE OF cur%ROWTYPE INDEX BY BINARY_INTEGER;';
            wk_sql := wk_sql ||'  rec_tbl cur_tbl;';
            wk_sql := wk_sql ||'  PROCEDURE bulk_copy IS';
            wk_sql := wk_sql ||'    PRAGMA AUTONOMOUS_TRANSACTION;';
            wk_sql := wk_sql ||'  BEGIN';
            wk_sql := wk_sql ||'    FORALL i IN 1 .. rec_tbl.COUNT';
            wk_sql := wk_sql ||'      INSERT INTO '|| wk_snapshot_name ||' VALUES rec_tbl(i);';
            wk_sql := wk_sql ||'    COMMIT;';
            wk_sql := wk_sql ||'  END;';
            wk_sql := wk_sql ||'BEGIN';
            wk_sql := wk_sql ||'  OPEN cur;';
            wk_sql := wk_sql ||'  LOOP';
            wk_sql := wk_sql ||'    FETCH cur BULK COLLECT INTO rec_tbl LIMIT '|| NVL(bluk_size, 100) ||';';
            wk_sql := wk_sql ||'    EXIT WHEN rec_tbl.COUNT = 0;';
            wk_sql := wk_sql ||'    bulk_copy();';
            wk_sql := wk_sql ||'  END LOOP;';
            wk_sql := wk_sql ||'  CLOSE cur;';
            wk_sql := wk_sql ||'END;';

            EXECUTE IMMEDIATE wk_sql;
        END;

BEGIN
    wk_table_name := UPPER(table_name);
    wk_snapshot_name := UPPER(snapshot_name);
    IF wk_snapshot_name IS NULL THEN
        wk_snapshot_name := SUBSTR(wk_table_name ||'$'|| TO_CHAR(SYSDATE, 'MMDDHH24MISS'), 1, NVL(max_identifier_length, 128));
    END IF;

    IF NVL(uncheck_dollar_sign, '0') = '0' AND
        NVL(INSTR(wk_snapshot_name, '$'), 0) = 0 THEN  RAISE_APPLICATION_ERROR(-20000, 'スナップショット名に「$」を含めてください。');
    END IF;

    IF wk_table_name = wk_snapshot_name THEN
        RAISE_APPLICATION_ERROR(-20000, '対象テーブル名とスナップショット名が同じです。');
    END IF;

    IF is_table_exists(wk_table_name) = FALSE THEN
        RAISE_APPLICATION_ERROR(-20000, 'テーブル「' || wk_table_name ||'」は存在しません。');
    END IF;

    create_snapshot_table();

    data_copy();
END;
/

 セットアップ上記コードを実行してください。
このプロシージャの実行にはCREATE ANY TABLE権限が必要になります。権限がない場合は付与してください。

GRANT CREATE ANY TABLE TO <ユーザ名>;

 使用方法DECLARE
BEGIN
   -- レコードを追加
   INSERT INTO T_SAMPLE(KEY, VALUE) VALUES ('01', 'HOGE');
   -- この時点のT_SAMPLEを複製する
   SP_TABLE_SNAPSHOT('T_SAMPLE');
   -- ロールバックする
   ROLLBACK;  
END;
/

-- 複製されたテーブルを検索
SELECT * FROM T_SAMPLE$0925160952;

KEY  VALUE
---- -----
01   HOGE    -- レコードが残っている

 応用方法-- 指定した条件だけ取得する
SP_TABLE_SNAPSHOT('T_SAMPLE', 'AGE <= 20 AND LAST_NAME = ''太郎'' ');

-- スナップショット名を指定する
SP_TABLE_SNAPSHOT('T_SAMPLE', snapshot_name => 'T_SAMPLE$COPY');
-- スナップショット名には $ を含めるよう強制してます。
-- 誤って実在テーブルを指定してしまうと悲惨な事になってしまうので予防策です。
-- $ を含めたくない場合は uncheck_dollar_sign => 1 を指定するか、コードを書き換えてください。

 アンインストールプロシージャを削除するだけです。

DROP PROCEDURE SP_TABLE_SNAPSHOT;

 使用上の注意点コードを見て頂ければわかると思いますが、愚直にデータをINSERTしてるだけなので複製するレコード件数が多ければ多いほど実行速度は低下します。開発環境であれば特に気にする必要はないと思いますが、本番環境には向かないと思います。デバッグ用としてご使用ください。
コード

セットアップ

使用方法

応用方法

アンインストール

使用上の注意点

Discussion
