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 结合使用的语法。

- Alan Storm

---

此功能终于在Oracle 23c中推出 - https://renenyffenegger.ch/notes/development/databases/Oracle/Releases-and-versions/23c/index - Dr Y Wit

Answer 1

最好和最有效的方法是捕获“表不存在”的异常：这避免了检查表是否存在两次的开销；并且不会遇到如果DROP由于其他原因（可能很重要）失败，异常仍然会被传递给调用者的问题：

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

23c 语法 自从 版本23c，Oracle 支持了更简单的 IF EXISTS 语法来处理所有的 drop DDL:

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

附录 以下是其他对象类型的等效块的参考：

序列

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;

- Jeffrey Kemp

---

要删除一个用户，需要忽略的SQLCODE是-1918。 - Andrew Swan

需要写一个过程来做那件事吗？有没有更好的方法来做呢？ - Wilson Freitas

如果我在脚本中为每个表添加许多 EXECUTE IMMEDIATE 'DROP TABLE mytable'; 语句，我是需要为每一个语句都放置一个异常处理程序，还是把所有语句包含在一个 BEGIN ... EXCEPTION ... END; 块中就足够了？ - Throoze

MS SQL的等价语句为IF OBJECT_ID('TblName') IS NOT NULL DROP TABLE TblName。SQL语言的冗长程度似乎与其价格成正比。 - user565869

@JeffreyKemp，你可能没有想到，但我一次又一次地发现Oracle让所有事情都变得非常困难。当你花费平均每个模糊的语法错误一个小时的时间，或者试图弄清楚如何做另一个数据库中明显且容易的事情（比如有条件地删除元素），而这些问题每天都会出现时，它们会快速积累。 - jpmc26

Answer 2

declare
   c int;
begin
   select count(*) into c from user_tables where table_name = upper('table_name');
   if c = 1 then
      execute immediate 'drop table table_name';
   end if;
end;

这是用于检查当前模式下是否存在表格。 如果要检查给定表格是否已经存在于不同的模式中，您需要使用all_tables而不是user_tables，并添加条件all_tables.owner = upper('模式名称')

- Marius Burz

---

+1 这种方式更好，因为不依赖于异常解码来理解该做什么。代码将更易于维护和理解。 - daitangio

同意@daitangio的观点-在只运行一次的部署脚本中，性能通常不会胜过可维护性。 - pettys

我很想了解隐式提交是否在这里起作用。您希望SELECT和DROP在同一个事务中。[显然忽略可能执行的任何后续DDL。] - Mathew

@Matthew，DROP是DDL命令，因此它将首先发出COMMIT，删除表，然后发出第二个COMMIT。当然，在这个例子中没有事务（因为只发出了一个查询），所以没有任何区别；但如果用户之前发出了一些DML，则在执行任何DDL之前将隐式提交。 - Jeffrey Kemp

Answer 3

我也一直在寻找相同的解决方案，但最终我编写了一个过程来帮助我实现：

CREATE OR REPLACE PROCEDURE DelObject(ObjName varchar2,ObjType varchar2)
IS
 v_counter number := 0;   
begin    
  if ObjType = 'TABLE' then
    select count(*) into v_counter from user_tables where table_name = upper(ObjName);
    if v_counter > 0 then          
      execute immediate 'drop table ' || ObjName || ' cascade constraints';        
    end if;   
  end if;
  if ObjType = 'PROCEDURE' then
    select count(*) into v_counter from User_Objects where object_type = 'PROCEDURE' and OBJECT_NAME = upper(ObjName);
      if v_counter > 0 then          
        execute immediate 'DROP PROCEDURE ' || ObjName;        
      end if; 
  end if;
  if ObjType = 'FUNCTION' then
    select count(*) into v_counter from User_Objects where object_type = 'FUNCTION' and OBJECT_NAME = upper(ObjName);
      if v_counter > 0 then          
        execute immediate 'DROP FUNCTION ' || ObjName;        
      end if; 
  end if;
  if ObjType = 'TRIGGER' then
    select count(*) into v_counter from User_Triggers where TRIGGER_NAME = upper(ObjName);
      if v_counter > 0 then          
        execute immediate 'DROP TRIGGER ' || ObjName;
      end if; 
  end if;
  if ObjType = 'VIEW' then
    select count(*) into v_counter from User_Views where VIEW_NAME = upper(ObjName);
      if v_counter > 0 then          
        execute immediate 'DROP VIEW ' || ObjName;        
      end if; 
  end if;
  if ObjType = 'SEQUENCE' then
    select count(*) into v_counter from user_sequences where sequence_name = upper(ObjName);
      if v_counter > 0 then          
        execute immediate 'DROP SEQUENCE ' || ObjName;        
      end if; 
  end if;
end;

希望这有所帮助。

- Robert Vabo

---

在我创建了上述的存储过程delobject之后，我尝试使用以下SQL语句来调用它。但是它没有起作用。delobject('MyTable', 'TABLE');

我得到了以下错误信息

Error starting at line 1 in command: delobject('MyTable', 'TABLE') Error report: Unknown Command - Shai

使用EXECUTE命令 - EXECUTE DelObject('MyTable', 'TABLE'); - Idan Yehuda

我更喜欢这个解决方案，而且你首先对对象进行检查以确保其存在，这可以防止 SQL 注入。此外，我想在单元测试的一部分中创建对象后检查其是否存在。 - CashCow

Answer 4

我只是想发布一个完整的代码，使用Jeffrey的代码创建一个表格并在它已经存在的情况下将其删除（向他致敬，而不是我！）。

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

    EXECUTE IMMEDIATE 'CREATE TABLE tablename AS SELECT * FROM sourcetable WHERE 1=0';

END;

- mishkin

---

个人而言，我会将创建表的步骤放在单独的步骤中，因为它不需要动态执行，并且不需要异常处理程序。 - Jeffrey Kemp

Answer 5

使用SQL*PLUS时，您还可以使用WHENEVER SQLERROR命令：

WHENEVER SQLERROR CONTINUE NONE
DROP TABLE TABLE_NAME;

WHENEVER SQLERROR EXIT SQL.SQLCODE
DROP TABLE TABLE_NAME;

使用 CONTINUE NONE 会报告一个错误，但脚本将继续执行。使用 EXIT SQL.SQLCODE 在出现错误时脚本将被终止。

另请参见：WHENEVER SQLERROR 文档

- trunkc

Answer 6

另一种方法是定义一个异常，然后只捕获这个异常，让其他所有异常继续传播。

Declare
   eTableDoesNotExist Exception;
   PRAGMA EXCEPTION_INIT(eTableDoesNotExist, -942);
Begin
   EXECUTE IMMEDIATE ('DROP TABLE myschema.mytable');
Exception
   When eTableDoesNotExist Then
      DBMS_Output.Put_Line('Table already does not exist.');
End;

- Leigh Riffel

---

@Sk8erPeter "已经不存在" vs. "曾经存在，但现在不再存在" :) - Jeffrey Kemp

我认为在运行管理脚本时，ALTER SESSION SET PLSQL_WARNINGS = 'ENABLE:ALL';是最佳实践，这个解决方案避免了编译器警告PLS-06009，而这个警告在被接受的答案中会出现："OTHERS handler does not end in RAISE or RAISE_APPLICATION_ERROR"。 - durette

Answer 7

我更喜欢采用经济解决方案。

BEGIN
    FOR i IN (SELECT NULL FROM USER_OBJECTS WHERE OBJECT_TYPE = 'TABLE' AND OBJECT_NAME = 'TABLE_NAME') LOOP
            EXECUTE IMMEDIATE 'DROP TABLE TABLE_NAME';
    END LOOP;
END;

- Pavel S

---

如果select语句先返回外键约束所参考的子表之前的父表，则会导致此处失败。 - ryvantage

Answer 8

一种方法是使用DBMS_ASSERT.SQL_OBJECT_NAME：

此函数验证输入参数字符串是否为现有SQL对象的合格SQL标识符。

DECLARE
    V_OBJECT_NAME VARCHAR2(30);
BEGIN
   BEGIN
        V_OBJECT_NAME  := DBMS_ASSERT.SQL_OBJECT_NAME('tab1');
        EXECUTE IMMEDIATE 'DROP TABLE tab1';

        EXCEPTION WHEN OTHERS THEN NULL;
   END;
END;
/

DBFiddle Demo

- Lukasz Szozda

---

但它可能不是表的名称。 - Jeffrey Kemp

在不同的模式中可能还有使用该名称的各种表。 - Hybris95

Answer 9

Oracle中没有'DROP TABLE IF EXISTS'，您需要使用SELECT语句。

尝试这个（我不熟悉Oracle语法，如果我的变量有问题，请原谅我）：

declare @count int
select @count=count(*) from all_tables where table_name='Table_name';
if @count>0
BEGIN
    DROP TABLE tableName;
END

- Erich

---

我尝试将脚本翻译为Oracle语法。 - Tom

声明 count number; 开始 从 all_tables 表中选择计算所有表名称为 'x' 的数量并存储在变量 count 中; 如果 count 大于 0，则执行动态 SQL "drop table x"； 结束。您无法直接从事务块中运行 DDL，必须使用 execute。 - Khb

非常感谢！我之前没有意识到语法有这么大的不同。我知道你需要将整个内容包裹在begin/end中，但我认为它是在另一个脚本的中间运行。汤姆：我决定保留我的版本而不是复制你的，这样我就不会从你那里夺走任何票数，因为你显然有正确的答案。 - Erich

我认为这个不会编译。在这里包括模式所有者可能也很重要，否则您可能会得到一个与同名表格无关的“true”。 - Allen

您的答案在发布后10分钟被正确的Oracle语法所取代。 - jpmc26

Answer 10

像这样的代码块可能对你有用。

DECLARE
    table_exist INT;

BEGIN
    SELECT Count(*)
    INTO   table_exist
    FROM   dba_tables
    WHERE  owner = 'SCHEMA_NAME' 
    AND table_name = 'EMPLOYEE_TABLE';

    IF table_exist = 1 THEN
      EXECUTE IMMEDIATE 'drop table EMPLOYEE_TABLE';
    END IF;
END;  

- user9213118