Oracle存储过程-删除所有序列

在跑数据库脚本之前,

往往会重新删除数据库中所有的序列.

 

下面这种方式就提供一个存储过程，能做到删除所有的序列。

在创建序列之前调用此存储过程即可。

 

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

--删除所有序列的存储过程;  
create or replace procedure PROC_DROPSEQUENCE_All
as 
  --引用user_sequences表中的sequenceName的类型;
  sequenceName user_sequences.sequence_name%type;  
  type ty is record(sequence_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 sequence_name from user_sequences;
         loop
             --从游标中获取一条记录,放入变量中;
             fetch ref_t into sequenceName;
                    SELECT COUNT(*) INTO mycount FROM user_sequences WHERE sequence_name = sequenceName;
                    if mycount>0 then
                       execute immediate 'DROP SEQUENCE '||sequenceName;
                    end if;
             exit when ref_t%notfound;  --退出;
         end loop;
     close ref_t;    
  end;
/  

 可以保存一个.sql文件放在批处理里面执行。

 

 

--删除单个序列的存储过程，需要传入一个序列的名称;

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