Oracle: 如果表格存在
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
结合起来的语法。
admin 更改状态以发布 2023年5月21日
最好,也是最有效的方法是捕获“表未找到”的异常:这样可以避免检查表是否存在两次的开销;并且不会遇到如果删除出现其他问题(可能很重要),异常仍然会返回给调用者的问题:
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;