使用存储过程清空oracle中所有的表的数据
create or replace procedure del_all isbegin--禁用所有主外键for c in (select t.constraint_name, t.table_name from USER_CONSTRAINTS t where t.constraint_type = 'R') loop EXECUTE IMMEDIATE'alter table '||c.table_name||' DISABLE CONSTRAINT '|| c.constraint_name;end loop;--truncate table 清空所有表for c1 in (select table_name from user_tables) loop EXECUTE IMMEDIATE'truncate table ' || c1.table_name;end loop;--启用所有主外键for c2 in (select t.constraint_name, t.table_name from USER_CONSTRAINTS t where t.constraint_type = 'R') loop EXECUTE IMMEDIATE'alter table' || c2.table_name || ' ENABLE CONSTRAINT ' || c2.constraint_name;end loop;end del_all;
页:
[1]