【Oracle PL/SQL】不要なテーブルを動的に一括削除する

スポンサードリンク

どうも、しぐま（@sigma__blog）です。

PGMの開発をしていると、テスト環境でとあるテーブルのコピーを作成して、そのテーブルを一時的に使用するなんてことが多々ある。

一時的に使うだけだから使い終わったら削除しなきゃいけないんだけど、ついつい忘れてて気が付いたら自分の作った不要なテーブルが10数個ある、みたいなことがあったりする。（本当は忘れてはいなくて面倒くさいから後回しにしてるだけ）

これだけ削除するテーブルが増えるとSQLを書いて1つずつ削除するのは手間だから、PL/SQLで削除対象のテーブルを動的に削除するスクリプトを書いてみた。

例えば、以下のようにテーブルが存在していたとする。

今回は「_TEST」のテーブルを削除対象とみなして、動的に削除していく。

PL/SQLはこんな感じで書いてみた。

DECLARE
    --
    -- 削除対象のテーブル情報を格納するテーブル型変数を定義
    TYPE type_user_tables
    IS
    TABLE Of user_tables%ROWTYPE INDEX BY PLS_INTEGER;
    --
    tbl_user_tables type_user_tables;
    --
    vSQL   VARCHAR2(4000);
    --
BEGIN
    --
    -- USER_TABLESより、削除対象のレコードをテーブル型変数に格納していく
    SELECT * BULK COLLECT INTO tbl_user_tables 
    FROM USER_TABLES 
    WHERE table_name LIKE '%\_TEST' ESCAPE '\'; -- この部分は削除対象のテーブル名に合わせて適宜変更する！
    --
    -- ループ処理でテーブルを削除していく
    FOR i IN 1..tbl_user_tables.COUNT LOOP
        --
        vSQL := 'DROP TABLE ' || tbl_user_tables( i ).table_name || ' CASCADE CONSTRAINTS PURGE';
        --
        EXECUTE IMMEDIATE vSQL;
        --
    END LOOP;
    --
END;
/