SQL Oracle：如果表存在
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1799128/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Oracle: If Table Exists
提问by Alan Storm
I'm writing some migration scripts for an Oracle database, and was hoping Oracle had something similar to MySQL's IF EXISTSconstruct.
我正在为 Oracle 数据库编写一些迁移脚本，并希望 Oracle 具有类似于 MySQL 的IF EXISTS构造的东西。
Specifically, whenever I want to drop a table in MySQL, I do something like
具体来说，每当我想在 MySQL 中删除一个表时，我都会做类似的事情
DROP TABLE IF EXISTS `table_name`;
This way, if the table doesn't exist, the DROPdoesn't produce an error, and the script can continue.
这样，如果表不存在，DROP则不会产生错误，脚本可以继续。
Does Oracle have a similar mechanism? I realize I could use the following query to check if a table exists or not
Oracle 有类似的机制吗？我意识到我可以使用以下查询来检查表是否存在
SELECT * FROM dba_tables where table_name = 'table_name';
but the syntax for tying that together with a DROPis escaping me.
但是将它与 a 联系在一起的语法DROP正在逃避我。
回答by Jeffrey Kemp
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:
最好和最有效的方法是捕获“找不到表”异常：这样可以避免检查表是否存在两次的开销；并且不会遇到以下问题：如果 DROP 由于其他原因（这可能很重要）而失败，则仍会向调用者提出异常：
BEGIN
EXECUTE IMMEDIATE 'DROP TABLE ' || table_name;
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE != -942 THEN
RAISE;
END IF;
END;
ADDENDUMFor reference, here are the equivalent blocks for other object types:
附录作为参考，以下是其他对象类型的等效块：
Sequence
序列
BEGIN
EXECUTE IMMEDIATE 'DROP SEQUENCE ' || sequence_name;
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE != -2289 THEN
RAISE;
END IF;
END;
View
看法
BEGIN
EXECUTE IMMEDIATE 'DROP VIEW ' || view_name;
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE != -942 THEN
RAISE;
END IF;
END;
Trigger
扳机
BEGIN
EXECUTE IMMEDIATE 'DROP TRIGGER ' || trigger_name;
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE != -4080 THEN
RAISE;
END IF;
END;
Index
指数
BEGIN
EXECUTE IMMEDIATE 'DROP INDEX ' || index_name;
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE != -1418 THEN
RAISE;
END IF;
END;
Column
柱子
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;
Database Link
数据库链接
BEGIN
EXECUTE IMMEDIATE 'DROP DATABASE LINK ' || dblink_name;
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE != -2024 THEN
RAISE;
END IF;
END;
Materialized View
物化视图
BEGIN
EXECUTE IMMEDIATE 'DROP MATERIALIZED VIEW ' || mview_name;
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE != -12003 THEN
RAISE;
END IF;
END;
Type
类型
BEGIN
EXECUTE IMMEDIATE 'DROP TYPE ' || type_name;
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE != -4043 THEN
RAISE;
END IF;
END;
Constraint
约束
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;
Scheduler Job
调度器作业
BEGIN
DBMS_SCHEDULER.drop_job(job_name);
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE != -27475 THEN
RAISE;
END IF;
END;
User / Schema
用户/架构
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;
Package
包裹
BEGIN
EXECUTE IMMEDIATE 'DROP PACKAGE ' || package_name;
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE != -4043 THEN
RAISE;
END IF;
END;
Procedure
程序
BEGIN
EXECUTE IMMEDIATE 'DROP PROCEDURE ' || procedure_name;
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE != -4043 THEN
RAISE;
END IF;
END;
Function
功能
BEGIN
EXECUTE IMMEDIATE 'DROP FUNCTION ' || function_name;
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE != -4043 THEN
RAISE;
END IF;
END;
Tablespace
表空间
BEGIN
EXECUTE IMMEDIATE 'DROP TABLESPACE' || tablespace_name;
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE != -959 THEN
RAISE;
END IF;
END;
Synonym
代名词
BEGIN
EXECUTE IMMEDIATE 'DROP SYNONYM ' || synonym_name;
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE != -1434 THEN
RAISE;
END IF;
END;
回答by Marius Burz
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;
That's for checking whether a table in the current schema exists.
For checking whether a given table already exists in a different schema, you'd have to use all_tablesinstead of user_tablesand add the condition all_tables.owner = upper('schema_name')
那是为了检查当前模式中的表是否存在。要检查给定的表是否已存在于不同的模式中，您必须使用all_tables代替user_tables并添加条件all_tables.owner = upper('schema_name')
回答by Robert Vabo
I have been looking for the same but I ended up writing a procedure to help me out:
我一直在寻找相同的东西，但我最终编写了一个程序来帮助我：
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;
Hope this helps
希望这可以帮助
回答by mishkin
just wanted to post a full code that will create a table and drop it if it already exists using Jeffrey's code (kudos to him, not me!).
只是想发布一个完整的代码，该代码将使用杰弗里的代码创建一个表并删除它（如果它已经存在）（感谢他，而不是我！）。
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;
回答by trunkc
With SQL*PLUS you can also use the WHENEVER SQLERROR command:
通过 SQL*PLUS，您还可以使用 WHENEVER SQLERROR 命令：
WHENEVER SQLERROR CONTINUE NONE
DROP TABLE TABLE_NAME;
WHENEVER SQLERROR EXIT SQL.SQLCODE
DROP TABLE TABLE_NAME;
With CONTINUE NONEan error is reported, but the script will continue. With EXIT SQL.SQLCODEthe script will be terminated in the case of an error.
随着CONTINUE NONE报告错误，但脚本会继续。随着EXIT SQL.SQLCODE该脚本将在一个错误的情况下被终止。
see also: WHENEVER SQLERROR Docs
另请参阅：WHENEVER SQLERROR 文档
回答by Erich
There is no 'DROP TABLE IF EXISTS' in oracle, you would have to do the select statement.
oracle 中没有“DROP TABLE IF EXISTS”，您必须执行 select 语句。
try this (i'm not up on oracle syntax, so if my variables are ify, please forgive me):
试试这个（我不熟悉 oracle 语法，所以如果我的变量是 ify，请原谅我）：
declare @count int
select @count=count(*) from all_tables where table_name='Table_name';
if @count>0
BEGIN
DROP TABLE tableName;
END
回答by Pavel S
I prefer following economic solution
我更喜欢以下经济解决方案
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;
回答by Lukasz Szozda
One way is to use DBMS_ASSERT.SQL_OBJECT_NAME :
一种方法是使用DBMS_ASSERT.SQL_OBJECT_NAME：
This function verifies that the input parameter string is a qualified SQL identifier of an existing SQL object.
此函数验证输入参数字符串是否为现有 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;
/
回答by Leigh Riffel
Another method is to define an exception and then only catch that exception letting all others propagate.
另一种方法是定义一个异常，然后只捕获该异常，让所有其他异常传播。
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;
回答by Mahesh Pandeya
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;
// Doing this code, checks if the table exists and later it creates the table max. this simply works in single compilation
// 执行此代码，检查表是否存在，然后创建表最大值。这仅适用于单一编译
