sql - Oracle:如果表存在

Question

我正在为 Oracle 数据库编写一些迁移脚本，并希望 Oracle 具有类似于 MySQL 的 IF EXISTS 构造的东西。

具体来说，每当我想删除 MySQL 中的表时，我都会执行类似的操作

DROP TABLE IF EXISTS `table_name`;

这样，如果表不存在，DROP 就不会产生错误，并且脚本可以继续。

Oracle有类似的机制吗？我意识到我可以使用以下查询来检查表是否存在

SELECT * FROM dba_tables where table_name = 'table_name';

但是将其与 DROP 结合在一起的语法却让我无法理解。

Answer 1

最好、最有效的方法是捕获“未找到表”异常:这可以避免两次检查表是否存在的开销；并且不会遇到以下问题:如果 DROP 由于某些其他原因(这可能很重要)而失败，则仍然会向调用者引发异常:

BEGIN
   EXECUTE IMMEDIATE 'DROP TABLE ' || table_name;
EXCEPTION
   WHEN OTHERS THEN
      IF SQLCODE != -942 THEN
         RAISE;
      END IF;
END;

23c 语法自 version 23c ，Oracle 支持所有 drop DDL 的更简单的 IF EXISTS 语法:

BEGIN
   EXECUTE IMMEDIATE 'DROP TABLE IF EXISTS ' || table_name;
END;

附录作为引用，以下是其他对象类型的等效 block :

序列

BEGIN
  EXECUTE IMMEDIATE 'DROP SEQUENCE ' || sequence_name;
EXCEPTION
  WHEN OTHERS THEN
    IF SQLCODE != -2289 THEN
      RAISE;
    END IF;
END;

查看

BEGIN
  EXECUTE IMMEDIATE 'DROP VIEW ' || view_name;
EXCEPTION
  WHEN OTHERS THEN
    IF SQLCODE != -942 THEN
      RAISE;
    END IF;
END;

触发器

BEGIN
  EXECUTE IMMEDIATE 'DROP TRIGGER ' || trigger_name;
EXCEPTION
  WHEN OTHERS THEN
    IF SQLCODE != -4080 THEN
      RAISE;
    END IF;
END;

索引

BEGIN
  EXECUTE IMMEDIATE 'DROP INDEX ' || index_name;
EXCEPTION
  WHEN OTHERS THEN
    IF SQLCODE != -1418 THEN
      RAISE;
    END IF;
END;

专栏

BEGIN
  EXECUTE IMMEDIATE 'ALTER TABLE ' || table_name
                || ' DROP COLUMN ' || column_name;
EXCEPTION
  WHEN OTHERS THEN
    IF SQLCODE != -904 AND SQLCODE != -942 THEN
      RAISE;
    END IF;
END;

数据库链接

BEGIN
  EXECUTE IMMEDIATE 'DROP DATABASE LINK ' || dblink_name;
EXCEPTION
  WHEN OTHERS THEN
    IF SQLCODE != -2024 THEN
      RAISE;
    END IF;
END;

物化 View

BEGIN
  EXECUTE IMMEDIATE 'DROP MATERIALIZED VIEW ' || mview_name;
EXCEPTION
  WHEN OTHERS THEN
    IF SQLCODE != -12003 THEN
      RAISE;
    END IF;
END;

类型

BEGIN
  EXECUTE IMMEDIATE 'DROP TYPE ' || type_name;
EXCEPTION
  WHEN OTHERS THEN
    IF SQLCODE != -4043 THEN
      RAISE;
    END IF;
END;

约束

BEGIN
  EXECUTE IMMEDIATE 'ALTER TABLE ' || table_name
            || ' DROP CONSTRAINT ' || constraint_name;
EXCEPTION
  WHEN OTHERS THEN
    IF SQLCODE != -2443 AND SQLCODE != -942 THEN
      RAISE;
    END IF;
END;

调度程序作业

BEGIN
  DBMS_SCHEDULER.drop_job(job_name);
EXCEPTION
  WHEN OTHERS THEN
    IF SQLCODE != -27475 THEN
      RAISE;
    END IF;
END;

用户/架构

BEGIN
  EXECUTE IMMEDIATE 'DROP USER ' || user_name;
  /* you may or may not want to add CASCADE */
EXCEPTION
  WHEN OTHERS THEN
    IF SQLCODE != -1918 THEN
      RAISE;
    END IF;
END;

封装

BEGIN
  EXECUTE IMMEDIATE 'DROP PACKAGE ' || package_name;
EXCEPTION
  WHEN OTHERS THEN
    IF SQLCODE != -4043 THEN
      RAISE;
    END IF;
END;

程序

BEGIN
  EXECUTE IMMEDIATE 'DROP PROCEDURE ' || procedure_name;
EXCEPTION
  WHEN OTHERS THEN
    IF SQLCODE != -4043 THEN
      RAISE;
    END IF;
END;

功能

BEGIN
  EXECUTE IMMEDIATE 'DROP FUNCTION ' || function_name;
EXCEPTION
  WHEN OTHERS THEN
    IF SQLCODE != -4043 THEN
      RAISE;
    END IF;
END;

表空间

BEGIN
  EXECUTE IMMEDIATE 'DROP TABLESPACE ' || tablespace_name;
EXCEPTION
  WHEN OTHERS THEN
    IF SQLCODE != -959 THEN
      RAISE;
    END IF;
END;

同义词

BEGIN
  EXECUTE IMMEDIATE 'DROP SYNONYM ' || synonym_name;
EXCEPTION
  WHEN OTHERS THEN
    IF SQLCODE != -1434 THEN
      RAISE;
    END IF;
END;