SQLとPL/SQLで「Oracle: If Table Exists」を実装する方法

2024-04-02

Oracleでテーブルが存在するかどうかを確認し、存在する場合にのみ削除するSQLとPL/SQLのコード解説

このページでは、SQLとPL/SQLそれぞれを使って、テーブルが存在するかどうかを確認し、存在する場合にのみ削除するコードの書き方を解説します。

対象読者

Oracleデータベースを操作する経験がある方
SQLとPL/SQLの基本的な構文を理解している方

SQLによるテーブル存在確認と削除

1 EXISTSキーワードを使う方法

SELECT 1
FROM user_tables
WHERE table_name = 'テーブル名';

-- テーブルが存在する場合は1が返される

IF EXISTS (
  SELECT 1
  FROM user_tables
  WHERE table_name = 'テーブル名'
) THEN
  DROP TABLE テーブル名;
END IF;

解説

EXISTSキーワードは、サブクエリが1行以上返した場合にTRUEを返す
user_tablesビューは、ユーザーが所有するすべてのテーブルの情報を持つ
table_nameは、削除したいテーブル名に置き換える

SELECT *
FROM ALL_TABLES
WHERE table_name = 'テーブル名';

-- テーブルが存在する場合は1行以上返される

IF (
  SELECT COUNT(*)
  FROM ALL_TABLES
  WHERE table_name = 'テーブル名'
) > 0 THEN
  DROP TABLE テーブル名;
END IF;

ALL_TABLESビューは、データベース内のすべてのテーブルの情報を持つ
COUNT(*)関数を使って、テーブルの存在を確認

PL/SQLによるテーブル存在確認と削除

1 DBMS_METADATAパッケージを使う方法

DECLARE
  l_table_name VARCHAR2(30);
BEGIN
  l_table_name := 'テーブル名';

  IF DBMS_METADATA.GET_TABLE_EXISTS(l_table_name) THEN
    DBMS_METADATA.DROP_TABLE(l_table_name);
  END IF;
END;

DBMS_METADATAパッケージは、データベースメタデータへのアクセスを提供
GET_TABLE_EXISTSプロシージャは、テーブルが存在するかどうかを確認
DROP_TABLEプロシージャは、テーブルを削除

2 EXCEPTIONを使う方法

DECLARE
  l_table_name VARCHAR2(30);
BEGIN
  l_table_name := 'テーブル名';

  BEGIN
    DROP TABLE l_table_name;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      NULL; -- テーブルが存在しない場合
  END;
END;

EXCEPTIONブロックを使って、DROP TABLE実行時のエラーを処理
NO_DATA_FOUND例外は、テーブルが存在しない場合に発生

まとめ

上記の方法を使い分けることで、状況に応じて安全かつ効率的にテーブルを削除できます。

シンプルな方法: EXISTSキーワードまたはALLキーワード
汎用的な方法: DBMS_METADATAパッケージ
エラー処理を重視する場合: EXCEPTION

注意事項

誤って重要なテーブルを削除しないよう、十分に注意してください。
削除前に、テーブルのバックアップを取ることを推奨します。

SQLによるテーブル存在確認と削除

-- テーブルが存在するかどうか確認
SELECT 1
FROM user_tables
WHERE table_name = 'TEST_TABLE';

-- テーブルが存在する場合は1が返される

IF EXISTS (
  SELECT 1
  FROM user_tables
  WHERE table_name = 'TEST_TABLE'
) THEN
  -- テーブルを削除
  DROP TABLE TEST_TABLE;
END IF;

-- テーブルが存在するかどうか確認
SELECT *
FROM ALL_TABLES
WHERE table_name = 'TEST_TABLE';

-- テーブルが存在する場合は1行以上返される

IF (
  SELECT COUNT(*)
  FROM ALL_TABLES
  WHERE table_name = 'TEST_TABLE'
) > 0 THEN
  -- テーブルを削除
  DROP TABLE TEST_TABLE;
END IF;

PL/SQLによるテーブル存在確認と削除

DECLARE
  l_table_name VARCHAR2(30);
BEGIN
  l_table_name := 'TEST_TABLE';

  -- テーブルが存在するかどうか確認
  IF DBMS_METADATA.GET_TABLE_EXISTS(l_table_name) THEN
    -- テーブルを削除
    DBMS_METADATA.DROP_TABLE(l_table_name);
  END IF;
END;

DECLARE
  l_table_name VARCHAR2(30);
BEGIN
  l_table_name := 'TEST_TABLE';

  BEGIN
    -- テーブルを削除
    DROP TABLE l_table_name;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      NULL; -- テーブルが存在しない場合
  END;
END;

実行方法

上記のコードをOracle SQL Developerなどのツールで実行します。
TEST_TABLEという名前のテーブルが存在する場合は、削除されます。
テーブルが存在しない場合は、何も起こりません。

上記のコードはサンプルです。実際の環境に合わせて変更してください。

Oracleでテーブルが存在するかどうかを確認し、存在する場合にのみ削除するその他の方法

USER_OBJECTSビューを使う

SELECT object_name
FROM user_objects
WHERE object_type = 'TABLE'
AND object_name = 'テーブル名';

-- テーブルが存在する場合は1行以上返される

IF (
  SELECT COUNT(*)
  FROM user_objects
  WHERE object_type = 'TABLE'
  AND object_name = 'テーブル名'
) > 0 THEN
  DROP TABLE テーブル名;
END IF;

USER_OBJECTSビューは、ユーザーが所有するすべてのオブジェクトの情報を持つ

SYS.DBA_TABLESビューを使う

SELECT table_name
FROM sys.dba_tables
WHERE table_name = 'テーブル名';

-- テーブルが存在する場合は1行以上返される

IF (
  SELECT COUNT(*)
  FROM sys.dba_tables
  WHERE table_name = 'テーブル名'
) > 0 THEN
  DROP TABLE テーブル名;
END IF;