首页 > 代码库 > Oracle 重置序列

Oracle 重置序列

Oracle将自增sequence重置为初始值1时,若是删除再重建,那么依赖它的函数和存储过程将失效,需要重新编译。这里利用相反的过程,利用负值步长参数,将递增值改为(sequence.NEXTVAL-1),然后再改回来。 假设需要修改的序列名:seq_name1select seq_name.nextval from dual; //假设得到结果56562alter sequence seq_name increment by -5655; //注意是-(n-1)3select seq_name.nextval from dual;//再查一遍,走一下,重置为1了4alter 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;

 

Oracle 重置序列