Oracle 存储过程删除和新增序列解决流水号每天重新开始

最新推荐文章于 2024-06-07 18:09:50 发布

sunjava1

最新推荐文章于 2024-06-07 18:09:50 发布

阅读量4.1k

点赞数 1

分类专栏： Oracle 文章标签： oracle

本文链接：https://blog.csdn.net/ontheway20/article/details/40399483

版权

Oracle 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

批量跑脚本的时候需要删除所有的序列，调用下面存储即可：

--删除所有序列的存储过程;    
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;

写一个数据库的定时job每天都调用下面的存储，解决流水号每天重新开始

create or replace procedure PROC_DROP_SEQUENCE as
  mycount number(10);
begin
  SELECT COUNT(*)
    INTO mycount
    FROM user_sequences
   WHERE sequence_name = 'BIZ_CODE_SEQ_RTN';
  if mycount > 0 then
    execute immediate 'DROP SEQUENCE BIZ_CODE_SEQ_RTN';
    execute immediate 'create sequence BIZ_CODE_SEQ_RTN
                      minvalue 1
                      maxvalue 999999999999999
                      start with 1
                      increment by 1
                      nocache
                      cycle';
  end if;
end;