嗨非常新的oracle,如何在序列号达到最大值时捕获并重置它：
这是序列脚本：
CREATE SEQUENCE idctr_seq MINVALUE 1 MAXVALUE 99 START WITH 1 INCREMENT BY 1 CACHE 10;
然后这是代码,当序列达到最大值时,它尝试将值重置为1.如果代码有效,我稍后会将其转换为函数,但它会失败.
declare
seq_num number;
exceed_maxvalue exception;
pragma exception_init(exceed_maxvalue,-8004);
begin
seq_num := idctr_seq.nextval;
DBMS_OUTPUT.PUT_LINE(seq_num);
exception
when exceed_maxvalue then
execute immediate 'sequence idctr_seq increment by -99 minvalue 0';
execute immediate 'select idctr_seq.nextval from dual';
execute immediate 'alter sequence idctr_seq increment by 1 minvalue 0';
end;
解决方法
这是
CYCLE关键字的用例;报价
indicate that the sequence continues to generate values after reaching either its maximum or minimum value. After an ascending sequence reaches its maximum value,it generates its minimum value. After a descending sequence reaches its minimum,it generates its maximum value.
您的对象声明应该变为：
CREATE SEQUENCE idctr_seq MINVALUE 1 MAXVALUE 99 START WITH 1 INCREMENT BY 1 CACHE 10 CYCLE;
这是一个较小序列的演示
sql> create sequence test_seq start with 1 maxvalue 3 nocache cycle; Sequence created. sql> select test_seq.nextval from dual; NEXTVAL ---------- 1 sql> select test_seq.nextval from dual; NEXTVAL ---------- 2 sql> select test_seq.nextval from dual; NEXTVAL ---------- 3 sql> select test_seq.nextval from dual; NEXTVAL ---------- 1
