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

具体来说,每当我想在MySQL中删除一个表时,我就会做这样的事情

DROP TABLE IF EXISTS `table_name`;

这样,如果表不存在,DROP也不会产生错误,脚本可以继续。

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

SELECT * FROM dba_tables where table_name = 'table_name';

但是把它和DROP绑定在一起的语法让我很困惑。


当前回答

我一直在寻找同样的东西,但我最终写了一个程序来帮助我:

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;

希望这能有所帮助

其他回答

只是想发布一个完整的代码,将创建一个表,并删除它,如果它已经存在使用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;

像这样的积木可能对你有用。

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;  

下面的代码片段对我很有用

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

在oracle中没有'DROP TABLE IF EXISTS',你必须执行select语句。

试试这个(我不上oracle语法,所以如果我的变量是ify,请原谅我):

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

遗憾的是没有,如果存在就删除,如果不存在就创建

您可以编写一个plsql脚本来包含其中的逻辑。

http://download.oracle.com/docs/cd/B12037_01/server.101/b10759/statements_9003.htm

我不太喜欢Oracle语法,但我认为@Erich的脚本应该是这样的。

declare 
cant integer
begin
select into cant count(*) from dba_tables where table_name='Table_name';
if count>0 then
BEGIN
    DROP TABLE tableName;
END IF;
END;