开始值不能改, declare vnumber number; nnumber number; begin select ( (select max(iid) from t1) - seq_test001.nextval) into vnumber from dual;
if vnumber<>0 then execute immediate 'ALTER SEQUENCE seq_test001 INCREMENT BY '||vnumber; select seq_test001.nextval into nnumber from dual; execute immediate 'ALTER SEQUENCE seq_test001 INCREMENT BY 1'; end if; end; /
SQL> create sequence se_1 start with 1;序列已创建。SQL> select se_1.nextval from dual; NEXTVAL
----------
1SQL> / NEXTVAL
----------
2SQL> / NEXTVAL
----------
3SQL> select se_1.currval from dual; CURRVAL
----------
3SQL> alter sequence se_1 increment by -2;序列已更改。SQL> select se_1.nextval from dual; NEXTVAL
----------
1SQL> select se_1.currval from dual; CURRVAL
----------
1
这个方法挺好,不过再将increment设置为-2之后,
需要select一下该序列的.nextval才能达到当前值减小的目的。
然后再将该序列的increment重置为1.能再简化些么?
declare
vnumber number;
nnumber number;
begin
select ( (select max(iid) from t1) - seq_test001.nextval) into vnumber from dual;
if vnumber<>0 then
execute immediate 'ALTER SEQUENCE seq_test001 INCREMENT BY '||vnumber;
select seq_test001.nextval into nnumber from dual;
execute immediate 'ALTER SEQUENCE seq_test001 INCREMENT BY 1';
end if;
end;
/