谢谢Oldwain的解答。我从同事那里找到了一个这样的函数,如下:
create or replace procedure exObjectsDrop_P(nil in integer := 0)
as
cursor exObject is select object_type,object_name
from user_objects
where object_type='PROCEDURE'
orobject_type='FUNCTION'
orobject_type='SEQUENCE'
orobject_type='TABLE'
orobject_type='VIEW'
order by object_type;
vSQLDrop varchar2(300);
vSQLErrm varchar2(600);
vSQLCode number;
vCursorID integer;
exam exObject%ROWTYPE;
begin
DBMS_OUTPUT.PUT_LINE('BEGIN TIME: '||to_char(sysdate));
open exObject;
vCursorID := DBMS_SQL.OPEN_CURSOR;
--*******************************************************************************************************************
loop
fetch exObject into exam;
exit when exObject%NOTFOUND;
begin
--***************************************************************************************************************
if upper(exam.object_name)upper('exObjectsDrop_P') then
vSQLDrop := 'drop '||exam.object_type||' '||exam.object_name;
end if;
DBMS_SQL.PARSE(vCursorID, vSQLDrop, DBMS_SQL.V7);
--***************************************************************************************************************
exception when others then
vSQLCode := SQLCODE;
vSQLErrm := SUBSTR(SQLERRM,1,600);
DBMS_OUTPUT.PUT_LINE('ERROR: '||' '||vSQLErrm);
end;
--***************************************************************************************************************
end loop;
--*******************************************************************************************************************
DBMS_SQL.CLOSE_CURSOR(vCursorID);
close exObject;
DBMS_OUTPUT.PUT_LINE('END TIME: '||to_char(sysdate));
end exObjectsDrop_P;