批量删除用户表

set serveroutput on;
--清空回收站
purge recyclebin;
begin
declare 
    dropCount integer;
    v_str varchar2(400);
    cursor m_grad is
      select *
        from user_objects
       where object_type in ('PACKAGE', 'TABLE', 'TYPE','FUNCTION') and object_name<>'mp_truncate_table';
       m_cous user_objects%rowtype;
  begin
    dropCount := 0;
    open m_grad; --打开游标
    loop
      fetch m_grad
        into m_cous;
      if m_grad%found then
        if m_cous.object_type = 'TABLE' then
          v_str := ' DROP  TABLE ' || m_cous.object_name || ' cascade   constraint purge';
        elsif m_cous.object_type = 'PACKAGE' then
          v_str := ' DROP  package ' ||m_cous.object_name;
        elsif m_cous.object_type = 'TYPE' then
          v_str := ' DROP  TYPE   ' ||m_cous.object_name;
        else
           v_str := ' DROP  FUNCTION   ' ||m_cous.object_name;
        end if;
 dropCount := dropCount + 1;
        dbms_output.put_line(v_str);
        execute immediate v_str;
        v_str := '';
      elsif m_grad%notfound then
        exit;
      end if;
    end loop;
dbms_output.put_line('共删除'||dropCount||'项，删除成功！');
  end;
end;
/

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/13571112/viewspace-624525/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/13571112/viewspace-624525/