使用存储过程完成级联删除的问题

[KGYH]

现在需要将数据库中的一部分表进行清空，但是发现表之间都是用外键进行关联，而且主子表关系非常复杂，很难手动删除，所以想通过存储过程方式完成。
通过递归的思路完成对主表的所有子表进行遍历

create  procedure del_all_subtabs(tablename varchar2) IS
    CURSOR c_tab(tab_name varchar2) is
      --该游标用于查找该主表下的所有子表
      select a.table_name
        from (select a.constraint_name,
                     b.table_name,
                     b.column_name,
                     a.r_constraint_name
                from user_constraints a, user_cons_columns b
               WHERE a.constraint_type = 'R'
                 and a.constraint_name = b.constraint_name) a,
             (select distinct a.r_constraint_name,
                              b.table_name,
                              b.column_name
                from user_constraints a, user_cons_columns b
               WHERE a.constraint_type = 'R'
                 and a.r_constraint_name = b.constraint_name) b
       where a.r_constraint_name = b.r_constraint_name
         and b.table_name = tab_name
         and a.table_name != tab_name
       order by b.table_name;

    v_tab_name  varchar2(50);
    v_sql       varchar2(100);
  begin
    open c_tab(tablename);
    loop
      fetch c_tab
        into v_tab_name;
      exit when c_tab%notfound;
      del_all_subtabs(v_tab_name);--递归循环，将下一级的子表清除
     
      v_sql := 'delete from ' || v_tab_name;
      execute immediate v_sql;--清除记录
      commit;
      
    end loop;
    close c_tab;
  end del_all_subtabs;

但是执行程序时发现，因为子表很多，结构复杂 所以出现了游标超过最大数的情况，是因为写的程序有问题？还是数据库游标数不合理？

希望高手能够解答啊~

[shy315]

递归打开游标，不说是否合理，用递归资源开销肯定会有风险。 针对这种游标过多情况，估计可以用connect by递归出所有结果，用一次游标； 或者将结果集放入列表，先全部delete，然后递归循环列表。

[a597926661]

有点复杂了 调试一下试试

[KGYH]

引用 1 楼 shy315 的回复:

递归打开游标，不说是否合理，用递归资源开销肯定会有风险。 针对这种游标过多情况，估计可以用connect by递归出所有结果，用一次游标； 或者将结果集放入列表，先全部delete，然后递归循环列表。

谢谢楼上啊，确实是一种思路，我尝试使用connect by来递归出结果。表有自引用，但是使用NOCYCLE却没用，还是查出循环数据了。。求解啊

[adaizi1980]

先停用数据库上的所有约束，然后再删除，删除完数据后，恢复约束 select * from all_constraints找到所有约束（系统表名不太记得了，你可以查下资料） ALTER TABLE 表名 disable constraint 约束名; --禁用约束 --删除数据 ... --恢复约束 ALTER TABLE 表名 disable constraint 约束名;

[forgetsam]

思路就是问题，建立约束的时候就可以设置成级联删除的。 再不济也要用触发器。