我试图使用一个过程(无参数)删除位于启动过程的模式中的所有用户创建的数据库对象,但我真的不知道如何解决这个问题。这是我到目前为止,但我认为这是错误的方式。
create or replace procedure CLEAN_SCHEMA is cursor schema_cur is select 'drop '||object_type||' '|| object_name|| DECODE(OBJECT_TYPE,'TABLE',' CASCADE CONSTRAINTS;',';') from user_objects; schema_rec schema_cur%rowtype; begin select 'drop '||object_type||' '|| object_name|| DECODE(OBJECT_TYPE,';') into schema_rec from user_objects; end; /
create or replace FUNCTION DROP_ALL_SCHEMA_OBJECTS RETURN NUMBER AS PRAGMA AUTONOMOUS_TRANSACTION; cursor c_get_objects is select object_type,'"'||object_name||'"'||decode(object_type,' cascade constraints',null) obj_name from user_objects where object_type in ('TABLE','VIEW','PACKAGE','SEQUENCE','SYNONYM','MATERIALIZED VIEW') order by object_type; cursor c_get_objects_type is select object_type,'"'||object_name||'"' obj_name from user_objects where object_type in ('TYPE'); BEGIN begin for object_rec in c_get_objects loop execute immediate ('drop '||object_rec.object_type||' ' ||object_rec.obj_name); end loop; for object_rec in c_get_objects_type loop begin execute immediate ('drop '||object_rec.object_type||' ' ||object_rec.obj_name); end; end loop; end; RETURN 0; END DROP_ALL_SCHEMA_OBJECTS;
select DROP_ALL_SCHEMA_OBJECTS from dual;
当你想删除所有的对象时,确保你不要尝试删除proc你的运行(我不关心procs,为什么我没有procs或函数在object_type列表中)
如果你想放弃所有你需要一个匿名块
但是我需要能够从一个只允许ansi sql(而不是plsql)这样一个存储过程的工具来做到这一点。
请享用。