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

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

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;