如何在单个PLSQL块中按FK、PK依赖顺序删除myschema下所有表

你原有代码存在三个核心问题：

• 没有过滤schema所有者，会查询到全库的约束信息
• 没有按依赖层级排序，直接遍历约束会出现重复删表、删除顺序错误的问题
• 仅生成了删除语句，没有实际执行的逻辑

下面是可直接运行的单PLSQL块实现，会自动按外键依赖顺序删除表，还支持扩展清理其他类型对象：

SET SERVEROUTPUT ON;
DECLARE
    lv_str VARCHAR2(1000);
    lv_owner VARCHAR2(30) := 'MYSCHEMA'; -- Oracle对象名默认大写，若schema为小写创建需对应调整
BEGIN
    -- 第一步：按依赖顺序删除有外键关联的表，依赖层级越高的子表优先删除
    FOR c IN (
        SELECT DISTINCT table_name
        FROM all_constraints
        WHERE owner = lv_owner
          AND constraint_type = 'R' -- 筛选外键约束
        CONNECT BY PRIOR table_name = r_constraint_name
        START WITH table_name IN (
            SELECT table_name FROM all_tables WHERE owner = lv_owner
        )
        ORDER SIBLINGS BY LEVEL DESC
    ) LOOP
        lv_str := 'DROP TABLE "' || lv_owner || '"."' || c.table_name || '" CASCADE CONSTRAINTS';
        DBMS_OUTPUT.PUT_LINE('执行语句：' || lv_str);
        -- 调试阶段可先注释下面这行，确认输出语句顺序正确后再打开执行删除
        EXECUTE IMMEDIATE lv_str;
    END LOOP;

    -- 第二步：删除没有任何外键依赖的独立表
    FOR c IN (
        SELECT table_name
        FROM all_tables
        WHERE owner = lv_owner
          AND table_name NOT IN (
              SELECT table_name FROM all_constraints WHERE owner = lv_owner AND constraint_type = 'R'
          )
    ) LOOP
        lv_str := 'DROP TABLE "' || lv_owner || '"."' || c.table_name || '" CASCADE CONSTRAINTS';
        DBMS_OUTPUT.PUT_LINE('执行语句：' || lv_str);
        EXECUTE IMMEDIATE lv_str;
    END LOOP;

    -- 可选扩展：清理模式下其他类型对象，按需打开注释即可
    /*
    -- 删除视图
    FOR c IN (SELECT view_name FROM all_views WHERE owner = lv_owner) LOOP
        EXECUTE IMMEDIATE 'DROP VIEW "' || lv_owner || '"."' || c.view_name || '"';
    END LOOP;
    -- 删除序列
    FOR c IN (SELECT sequence_name FROM all_sequences WHERE sequence_owner = lv_owner) LOOP
        EXECUTE IMMEDIATE 'DROP SEQUENCE "' || lv_owner || '"."' || c.sequence_name || '"';
    END LOOP;
    -- 删除存储过程/函数/包
    FOR c IN (SELECT object_name, object_type FROM all_objects WHERE owner = lv_owner AND object_type IN ('PROCEDURE','FUNCTION','PACKAGE','PACKAGE BODY')) LOOP
        EXECUTE IMMEDIATE 'DROP ' || c.object_type || ' "' || lv_owner || '"."' || c.object_name || '"';
    END LOOP;
    */
    DBMS_OUTPUT.PUT_LINE(lv_owner || ' 模式对象清理完成');
END;
/

注意事项 #

• 执行前请确认登录用户有DROP ANY TABLE等对应权限，或直接用myschema用户自身登录执行
• 语句中加CASCADE CONSTRAINTS是双重保险，即使依赖顺序有异常也不会触发约束报错
• 如果你的schema名、表名是小写创建（建对象时加了双引号），需要调整lv_owner变量的大小写，同时匹配语句中的对象名大小写

内容的提问来源于stack exchange，提问作者AlbertAlex