Sequence下个值回归1的代码

alter sequence S_BATCH increment by -20 nocache;
select S_BATCH.nextval from dual;
alter sequence S_BATCH increment by 1 nocache;
declare
LastValue integer;
begin
loop
select S_BATCH.currval into LastValue from dual;
exit when LastValue =0;
select S_BATCH.nextval into LastValue from dual;
end loop;
end;
/
alter sequence S_BATCH increment by 1 cache 20;

实际应用中 用以下代码比较方便，上面的是Oracle里直接取的

---系统流水号
select S_SERIALNO.nextval INTO LastValue from dual;
select S_SERIALNO.currval into LastValue from dual;
LastValue := -LastValue;

EXECUTE IMMEDIATE ' alter sequence S_SERIALNO increment by ' || LastValue ||
' nocache';

select S_SERIALNO.nextval INTO LastValue from dual;

EXECUTE IMMEDIATE 'alter sequence S_SERIALNO increment by 1 nocache';

COMMIT;

[@more@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/12180666/viewspace-1018329/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/12180666/viewspace-1018329/