oracle重置序列从1开始
oracle版本号为11g
create sequence seq_name; --创建一个叫seq_name的序列,默认从1开始,步长为1
select seq_name.NEXTVAL from dual;
select seq_name.NEXTVAL from dual;
select seq_name.NEXTVAL from dual;
select seq_name.NEXTVAL from dual; --运行四次,此时序列值为4
select seq_name.currval from dual; --显示当前序列值(4)
create or replace procedure seq_res_1 --创建存储过程
is
v_sql varchar2(200);
n number;
begin
select seq_name.CURRVAL into n from dual;
v_sql:='alter sequence seq_name increment by -'||n||' minvalue 0';
execute immediate v_sql;
select seq_name.nextval into n from dual;
v_sql:='alter sequence seq_name increment by '||1||' minvalue 0';
execute immediate v_sql;
end seq_res_1;
call seq_res_1(); --执行存储过程,使序列初始值变为0,步长变为1
select seq_name.NEXTVAL from dual; --此时序列值显示为1
这个存储过程就是先将索引步长变为-seq_name.currval,即当前序列的值的相反数,再调用seq_name.nextval,使序列向后退当前值的步数,变为0。这时再调用seq_name.nextval,就可以使序列值变为1了。