最好和最有效的方法是捕获“找不到表”异常:这样可以避免检查表是否存在两次的开销。并且不会遇到以下问题:如果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;