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

来源：互联网发布：卸载360游戏优化器编辑：程序博客网时间：2024/05/19 08:43

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

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

0 0