Oracle:如果表存在

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

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

DROP TABLE IF EXISTS `table_name`;

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

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

SELECT * FROM dba_tables where table_name = 'table_name';

但是将它与DROP绑定在一起的语法让我困惑。

614887 次浏览
小开

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

您可以编写一个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;
小开

在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
小开 
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('schema_name')

小开

您总是可以自己发现错误。

begin
execute immediate 'drop table mytable';
exception when others then null;
end;

过度使用它被认为是不好的实践，类似于其他语言中的空catch()。

< p >对< br > K < / p >
小开
最佳答案

最好和最有效的方法是捕捉“table not found”;Exception:这避免了检查表是否存在两次的开销;并且不会遇到DROP因为其他原因(可能很重要)失败时仍然会向调用者抛出异常的问题:

BEGIN
EXECUTE IMMEDIATE 'DROP TABLE ' || table_name;
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE != -942 THEN
RAISE;
END IF;
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;
小开

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

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
v_counter int;
begin
select count(*) into v_counter from dba_users where upper(username)=upper('UserSchema01');
if v_counter > 0 then
execute immediate 'DROP USER UserSchema01 CASCADE';
end if;
end;
/






CREATE USER UserSchema01 IDENTIFIED BY pa$$word
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp
QUOTA UNLIMITED ON users;


grant create session to UserSchema01;

还有一个TABLE的例子:

declare
v_counter int;
begin
select count(*) into v_counter from all_tables where upper(TABLE_NAME)=upper('ORDERS') and upper(OWNER)=upper('UserSchema01');
if v_counter > 0 then
execute immediate 'DROP TABLE UserSchema01.ORDERS';
end if;
end;
/
小开

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

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;
小开

使用SQL*PLUS，你也可以使用每当SQLERROR命令:

WHENEVER SQLERROR CONTINUE NONE
DROP TABLE TABLE_NAME;


WHENEVER SQLERROR EXIT SQL.SQLCODE
DROP TABLE TABLE_NAME;

使用CONTINUE NONE会报告一个错误，但是脚本将继续。使用EXIT SQL.SQLCODE，脚本将在出现错误的情况下终止。

参见:每当SQLERROR文档

小开 
BEGIN
EXECUTE IMMEDIATE 'DROP TABLE "IMS"."MAX" ';
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE != -942 THEN
RAISE;
END IF;
EXECUTE IMMEDIATE '
CREATE TABLE "IMS"."MAX"
(    "ID" NUMBER NOT NULL ENABLE,
"NAME" VARCHAR2(20 BYTE),
CONSTRAINT "MAX_PK" PRIMARY KEY ("ID")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "SYSAUX"  ENABLE
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "SYSAUX"  ';




END;

//执行这段代码，检查表是否存在，然后创建表max。这只适用于单次编译

小开
如果你想让它可重入并最小化删除/创建周期，你可以使用dbms_metadata缓存DDL。Get_ddl并使用如下构造重新创建所有内容: 声明 v_ddl varchar2 (4000); 开始 select dbms_metadata.get_ddl('TABLE'，'DEPT'，'SCOTT') [比较缓存的DDL并执行如果不匹配] 而其他人则例外 如果sqlcode = -31603，则 [获取并执行缓存的ddl] 其他的 提高; 如果; 结束; 这只是一个示例，里面应该有一个循环，DDL类型，名称和所有者是变量
小开

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

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;
小开

一种方法是使用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

小开

我更喜欢以下经济的解决方案

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;
小开

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

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

提交答案