oracle禁用外键后清空所有表数据

declare

  --所有外键约束

  cursor c1 is

    select t.table_name

          ,t.constraint_name

      from user_constraints t

     where t.owner = 'IVM'

       AND T.constraint_type = 'R';

  --所有业务表    

  cursor c2 is

    select table_name

      from user_tables t

     where t.TABLE_NAME not in

           ('INTERVIEWMETHOD', 'MENU', 'ROLE', 'ROLE_MUNU', 'SYSTEMUSER');

  stmt varchar2(4000);

begin

 

  --01-禁用外键约束

  for cc in c1 loop

    BEGIN

      stmt := 'alter table ' || cc.table_name || ' disable constraint ' ||

              cc.constraint_name;

      dbms_output.put_line(stmt);

      execute immediate stmt;

    end;

  end loop;

 

  --02-清空所有业务数据

  for cc in c2 loop

    BEGIN

      stmt := 'TRUNCATE table ' || cc.table_name;

      dbms_output.put_line(stmt);

      execute immediate stmt;

    end;

  end loop;

 

  --03-启用外键约束

  for cc in c1 loop

    BEGIN

      stmt := 'alter table ' || cc.table_name || ' enable constraint ' ||

              cc.constraint_name;

      dbms_output.put_line(stmt);

      execute immediate stmt;

    end;

  end loop;

 

  --异常处理

EXCEPTION

  WHEN OTHERS THEN

    dbms_output.put_line('error ' || stmt || ' sqlerr' || sqlerrm);

    rollback;

end;