日時とテーブル名を動的に指定して、データを削除するPL/SQL

テーブルの削除ジョブなどを作成する場合に、テーブルの削除日やカラム名を動的に指定して作成したい場合に使用できるPL/SQLを紹介します。

データ準備

テーブル定義

削除内容を登録したマスタテーブルの定義

SQL> create user TEST identified by test default tablespace USERS;
SQL> grant CREATE SESSION to TEST;
SQL> grant UNLIMITED TABLESPACE to TEST;
SQL> grant CREATE TABLE to TEST;
SQL> conn test/test
SQL> create table MASTER_DEL_TABLE (
  2    TABLE_NAME       varchar2(30) -- 削除対象のテーブル名
  3  , COLUMN_NAME      varchar2(30) -- 削除対象のテーブルの指定カラム名（日付型を想定）
  4  , RETENTION_DAYS   number(4)    -- 削除対象となるテーブルの保存日数
  5  );

削除対象のテーブルの定義

SQL> create table TEST_TABLE (
  2    TESTID     number not null
  3  , UPDATED    date default sysdate
  4  );
SQL> -- データの登録はしないが、ループ確認用にテーブルを設定
SQL> create table ZAIKO (
  2    TESTID     number not null
  3  , HAIKI_DATE date default sysdate
  4  );

データ登録

削除対象のテーブルをマスタに登録

SQL> -- TEST_TABLE の UPDATED 列が、3日より前の場合は削除
SQL> insert into MASTER_DEL_TABLE values ('TEST_TABLE','UPDATED',3);
SQL> -- ダミーデータ（その他例として登録）
SQL> insert into MASTER_DEL_TABLE values ('ZAIKO','UPDATED',3);
SQL> commit;
SQL>
SQL> select * from MASTER_DEL_TABLE;

TABLE_NAME                     COLUMN_NAME                    RETENTION_DAYS
------------------------------ ------------------------------ --------------
TEST_TABLE                     UPDATED                                     3
ZAIKO                          HAIKI_DATE                                  3

SQL> -- 削除データについても登録
SQL> -- 4-5日前 削除対象
SQL> insert into TEST_TABLE values (1,sysdate - 5);
SQL> insert into TEST_TABLE values (2,sysdate - 4);
SQL> -- 当日-3日前 削除対象外
SQL> insert into TEST_TABLE values (3,sysdate - 3);
SQL> insert into TEST_TABLE values (4,sysdate - 2);
SQL> insert into TEST_TABLE values (5,sysdate - 1);
SQL> insert into TEST_TABLE values (6,sysdate - 0);
SQL> commit;
SQL> alter session set nls_date_format = 'yyyy/mm/dd hh24:mi:ss';
SQL> select * from TEST_TABLE order by 1;

    TESTID UPDATED
---------- -------------------
         1 2016/06/15 18:26:02
         2 2016/06/16 18:26:03
         3 2016/06/17 18:25:56
         4 2016/06/18 18:25:56
         5 2016/06/19 18:25:57
         6 2016/06/20 18:25:57

削除の実行

PL/SQLを実行しデータの削除を行います。

SQL> -- SQL エラーの場合は戻り値 2 で SQL*Plus を終了する
SQL> whenever sqlerror exit 2
SQL> -- OS エラーの場合は戻り値 2 で SQL*Plus を終了する
SQL> whenever oserror exit 2
SQL> set serveroutput on
SQL> DECLARE
  2    -- マスタからデータを取得するカーソルを定義
  3    CURSOR cDelMst IS
  4      select TABLE_NAME
  5           , COLUMN_NAME
  6           , RETENTION_DAYS
  7        from MASTER_DEL_TABLE;
  8    -- 一時的に SQL 文を格納する変数
  9    vSQL   VARCHAR2(4000);
 10
 11  BEGIN
 12    -- カーソルで指定したマスタデータをループし、1行ずつ処理を行う
 13    FOR vRec IN cDelMst LOOP
 14      -- 表名、列名は解析用に先に文字列化を行う。日付はバインド変数による実行計画対策。
 15      vSQL := 'DELETE FROM ' || vRec.TABLE_NAME || ' WHERE ' || vRec.COLUMN_NAME || ' < TRUNC(SYSDATE) - :1';
 16
 17      -- SQL 文を出力
 18      DBMS_OUTPUT.PUT_LINE('SQL=' || vSQL);
 19      DBMS_OUTPUT.PUT_LINE('Table=' || vRec.TABLE_NAME ||', Column='|| vRec.COLUMN_NAME ||', Days='|| vRec.RETENTION_DAYS);
 20
 21      -- SQL の実行
 22      EXECUTE IMMEDIATE vSQL
 23        USING IN vRec.RETENTION_DAYS;
 24
 25      COMMIT;
 26    END LOOP;
 27
 28  EXCEPTION
 29    WHEN NO_DATA_FOUND THEN
 30      -- 例外を無視
 31      DBMS_OUTPUT.PUT_LINE('NO_DATA_FOUND');
 32
 33    WHEN OTHERS THEN
 34      DBMS_OUTPUT.PUT_LINE('SQL Error: PL/SQLでエラーが発生しました。');
 35      RAISE;
 36  END;
 37  /
SQL=DELETE FROM TEST_TABLE WHERE UPDATED < TRUNC(SYSDATE) - :1
Table=TEST_TABLE, Column=UPDATED, Days=3
SQL=DELETE FROM ZAIKO WHERE HAIKI_DATE < TRUNC(SYSDATE) - :1
Table=ZAIKO, Column=HAIKI_DATE, Days=3

PL/SQL procedure successfully completed.

指定された期間が消えていることを確認します。TESTID 1と2（4日前と5日前）のデータが消えていることが確認出来ます。

SQL> select * from TEST_TABLE order by 1;

    TESTID UPDATED
---------- -------------------
         3 2016/06/17 18:25:57
         4 2016/06/18 18:25:57
         5 2016/06/19 18:25:57
         6 2016/06/20 18:25:57

上記でテーブルに保存した対象テーブルの指定期間のデータ削除ができるようになりました。
実際の運用で使用する場合は、エラーになったらその場で止まるなどするため、エラーケースを考えて実装してください。