Help us understand the problem. What is going on with this article?

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

More than 3 years have passed since last update.

自分用にメモ

目的

  • 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だとこんなに長い。

Why do not you register as a user and use Qiita more conveniently?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
Comments
Sign up for free and join this conversation.
If you already have a Qiita account
Why do not you register as a user and use Qiita more conveniently?
You need to log in to use this function. Qiita can be used more conveniently after logging in.
You seem to be reading articles frequently this month. Qiita can be used more conveniently after logging in.
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away