Oracle将自增sequence重置为初始值1时,若是删除再重建,那么依赖它的函数和存储过程将失效,需要重新编译。
这里利用相反的过程,利用负值步长参数,将递增值改为(sequence.NEXTVAL-1),然后再改回来。
假设需要修改的序列名:seq_name
1、select seq_name.nextval from dual; //假设得到结果5656
2、alter sequence seq_name increment by -5655; //注意是-(n-1)
3、select seq_name.nextval from dual;//再查一遍,走一下,重置为1了
4、alter sequence seq_name increment by 1;//还原
以下是存储过程,传入序列名称:
CREATE OR REPLACE PROCEDURE SEQ_RESET(V_SEQNAME VARCHAR2) AS
N NUMBER(10);
TSQL VARCHAR2(100);
BEGIN
EXECUTE IMMEDIATE 'select ' || V_SEQNAME || '.nextval from dual'
INTO N;
N := - (N - 1);
TSQL := 'alter sequence ' || V_SEQNAME || ' increment by ' || N;
EXECUTE IMMEDIATE TSQL;
EXECUTE IMMEDIATE 'select ' || V_SEQNAME || '.nextval from dual'
INTO N;
TSQL := 'alter sequence ' || V_SEQNAME || ' increment by 1';
EXECUTE IMMEDIATE TSQL;
END SEQ_RESET;