[Oracle]存在するテーブルのみDROPする方法

自分用にメモ

目的

• DROP TABLE～時にOracleエラーを出したくない
• 複数テーブルの削除を自動で実行させたい

概要

1. all_tablesディクショナリから削除対象のテーブル名称を取得
2. Oracleのバージョンを確認
3. 1で取得したテーブルに対してDROP文を動的SQLで実行
4. 2で確認したバージョンが"10"以降であればPURGEも実行
5. 3,4をテーブル数分ループ

普通はCREATE TABLE～とDROP TABLE～はセットで1ファイルに保持しておくから、
こんな面倒なことはしなくてもいいはず。

※適用するスキーマやサーバを間違えると取り返しがつかないので、
　使用前に必ず環境の確認をしておくこと。

SQL

一応動的SQLの実行個所はコメントアウト。
初回は出力されたDDLを確認して本当に実行するかを決める。

dropExistTables.sql

DECLARE
        -- 削除対象のテーブル
        CURSOR table_list IS
            SELECT
                     a.table_name
            FROM
                all_tables a
            WHERE
                a.owner         = 'SCOTT'           -- OWNERを指定
            AND a.table_name    IN (                -- テーブルを指定
                                        'BONUS'
                                       ,'EMP'
                                       ,'DEPT'
                                    )
            ORDER BY
                 a.table_name;

    version     VARCHAR2(2);
BEGIN
    -- Oracleのバージョンを取得
    DBMS_OUTPUT.PUT_LINE('Oracleのバージョンを確認...');
    SELECT
         REPLACE(SUBSTR(version ,0,2) ,'.' ,'') AS major_version
    INTO version
    FROM
        product_component_version
    WHERE
        LOWER(product) LIKE 'oracle%';
    DBMS_OUTPUT.PUT_LINE('バージョンは ' || version);
    
-- 削除処理開始
    DBMS_OUTPUT.PUT_LINE('テーブル削除 ----> START');
    FOR vRec IN table_list LOOP
        DBMS_OUTPUT.PUT_LINE('DROP TABLE ' || vRec.table_name || ' CASCADE CONSTRAINTS');   -- DDL確認用：DROP
        -- Oracleのバージョンが10g以降⇒PURGEでテーブルを完全削除
        IF TO_NUMBER(version) >= 10 THEN
            DBMS_OUTPUT.PUT_LINE('PURGE TABLE ' || vRec.table_name);                        -- DDL確認用：PURGE
--            EXECUTE IMMEDIATE 'PURGE TABLE ' || vRec.table_name;
        END IF;
--        EXECUTE IMMEDIATE 'DROP TABLE ' || vRec.table_name || ' CASCADE CONSTRAINTS';
    END LOOP;
    DBMS_OUTPUT.PUT_LINE('テーブル削除 ----> END');

EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('----エラー発生！----------------');
        DBMS_OUTPUT.PUT_LINE('--SQLERRM:'||SQLERRM);
        DBMS_OUTPUT.PUT_LINE('--SQLCODE:'||SQLCODE);
END;
/

まとめ

間違っても本番環境には使えないシロモノなので、
開発環境のワークテーブル等を片づけるのによいかと。
他のDBMSだと
DROP TABLE IF EXISTS table_name
の1文で済むのに、Oracleだとこんなに長い。