Oracle:如果表存在 [英] Oracle: If Table Exists

问题描述

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

I'm writing some migration scripts for an Oracle database, and was hoping Oracle had something similar to MySQL's IF EXISTS construct.

具体地说，每当我想在MySQL中删除表时，我都会做类似的事情

Specifically, whenever I want to drop a table in MySQL, I do something like

DROP TABLE IF EXISTS `table_name`;

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

This way, if the table doesn't exist, the DROP doesn't produce an error, and the script can continue.

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

Does Oracle have a similar mechanism? I realize I could use the following query to check if a table exists or not

SELECT * FROM dba_tables where table_name = 'table_name';

但是将其与DROP绑定在一起的语法使我逃脱了.

but the syntax for tying that together with a DROP is escaping me.

推荐答案

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

The best and most efficient way is to catch the "table not found" exception: this avoids the overhead of checking if the table exists twice; and doesn't suffer from the problem that if the DROP fails for some other reason (that might be important) the exception is still raised to the caller:

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

附录 作为参考，以下是其他对象类型的等效块:

ADDENDUM For reference, here are the equivalent blocks for other object types:

序列

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;

材料化视图

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;

这篇关于Oracle:如果表存在的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！