Oracle存储过程-删除所有表

 

删除所有user_tables中的表 ,这样非常不安全，执行改存储过程则会非常的危险. 请谨慎使用.

Set ECHO ON
set define off     
SPOOL logs/create_procedure.log

--删除所有表的存储过程;  
create or replace procedure PROC_DROPTABLE_All
as 
  --引用user_tables表中的tableName的类型;
  tableName user_tables.table_name%type;  
  type ty is record(table_name varchar2(30));
  --定义ref类型游标;-强类型
  type ref_type is ref cursor return ty;
  ref_t ref_type;
  --定义变量存储数量;
  mycount number(10);
  begin
    --打开游标;
    open ref_t for select table_name from user_tables;
         loop
             --从游标中获取一条记录,放入变量中;
             fetch ref_t into tableName;
                    SELECT COUNT(*) INTO mycount FROM user_tables WHERE TABLE_NAME = tableName;
                    if mycount>0 then
                       execute immediate 'DROP TABLE '||tableName || ' CASCADE CONSTRAINT ';
                    end if;
             exit when ref_t%notfound;  --退出;
         end loop;
     close ref_t;    
  end;
/

 

----删除单个表的存储过程,需要传入一个表的名称;

create or replace procedure PROC_dropTable(tabName in varchar2)
as 
  --引用user_tables表中的tableName的类型;
  tableName user_tables.table_name%type;  
  mycount number(10);
  begin
    --把存储过程传过来的参数，赋值给tableName;
    tableName:= tabName;
    SELECT COUNT(*) INTO mycount FROM user_tables WHERE TABLE_NAME = tableName ;
    if mycount>0 then
       execute immediate 'DROP TABLE '||tableName;
    end if;
  end;  
  /