日時とテーブル名を動的に指定して、データを削除するPL/SQL
対応バージョン
Oracle Database 11.2-12.1
2016-06-20記事公開
テーブルの削除ジョブなどを作成する場合に、テーブルの削除日やカラム名を動的に指定して作成したい場合に使用できるPL/SQLを紹介します。
データ準備
テーブル定義
削除内容を登録したマスタテーブルの定義
|
1 2 3 4 5 6 7 8 9 10 |
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 ); |
削除対象のテーブルの定義
|
1 2 3 4 5 6 7 8 9 |
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 ); |
データ登録
削除対象のテーブルをマスタに登録
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 |
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を実行しデータの削除を行います。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 |
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日前）のデータが消えていることが確認出来ます。
|
1 2 3 4 5 6 7 8 |
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 |
上記でテーブルに保存した対象テーブルの指定期間のデータ削除ができるようになりました。
実際の運用で使用する場合は、エラーになったらその場で止まるなどするため、エラーケースを考えて実装してください。
