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@]