create or replace procedure drop_all_tables is
begin
declare
cursor c_ts is
select * from user_tables;
cursor c_cs is
select * from user_constraints where constraint_type = 'R';
v_sql varchar2(1000 );
begin
--删除外键约束
for c_c in c_cs loop
v_sql := 'alter table ' || c_c.table_name || ' drop constraint ' ||
c_c.constraint_name;
execute immediate v_sql;
end loop ;
--删除表
for c_t in c_ts loop
v_sql := 'drop table ' || c_t.table_name;
execute immediate v_sql;
end loop ;
exception
when others then
DBMS_OUTPUT.put_line( 'sqlcode : ' || sqlcode );
DBMS_OUTPUT.put_line( 'sqlerrm : ' || sqlerrm );
end;
end drop_all_tables;