我正在为Oracle数据库写一些迁移脚本，并希望Oracle有类似于MySQL的IF EXISTS构造的东西。
具体来说，每当我想在MySQL中删除一个表时，我就会做这样的事情
DROP TABLE IF EXISTS `table_name`;
这样，如果表不存在，DROP也不会产生错误，脚本可以继续。
Oracle有类似的机制吗?我意识到我可以使用下面的查询来检查表是否存在
SELECT * FROM dba_tables where table_name = 'table_name';
但是把它和DROP绑定在一起的语法让我很困惑。
当前回答
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')
其他回答
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')
我更倾向于指定表和模式所有者。
还要注意大小写敏感性。(见下文“上”条款)。
我扔了几个不同的对象，以表明它可以用在桌子之外的地方。
.............
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;
/
最好和最有效的方法是捕获“table not found”异常:这避免了检查表是否存在两次的开销;并且不会遇到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;
View
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;
Type
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;
And if you want to make it re-enterable and minimize drop/create cycles, you could cache the DDL using dbms_metadata.get_ddl and re-create everything using a construct like this: declare v_ddl varchar2(4000); begin select dbms_metadata.get_ddl('TABLE','DEPT','SCOTT') into v_ddl from dual; [COMPARE CACHED DDL AND EXECUTE IF NO MATCH] exception when others then if sqlcode = -31603 then [GET AND EXECUTE CACHED DDL] else raise; end if; end; This is just a sample, there should be a loop inside with DDL type, name and owner being variables.
在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
