sequence如果没有设置循环cycle,在超过了sequence的最大值后,将出现ORA-08004错误。
SQL> create sequence TEST_SEQ_NO_CYCLE
2 minvalue 1
3 maxvalue 200
4 start with 100
5 increment by 50
6 nocache;
Sequence created
SQL> SELECT TEST_SEQ_NO_CYCLE.NEXTVAL FROM DUAL;
NEXTVAL
----------
100
SQL> SELECT TEST_SEQ_NO_CYCLE.NEXTVAL FROM DUAL;
NEXTVAL
----------
150
SQL> SELECT TEST_SEQ_NO_CYCLE.NEXTVAL FROM DUAL;
NEXTVAL
----------
200
SQL> SELECT TEST_SEQ_NO_CYCLE.NEXTVAL FROM DUAL;
SELECT TEST_SEQ_NO_CYCLE.NEXTVAL FROM DUAL
ORA-08004: 序列 TEST_SEQ_NO_CYCLE.NEXTVAL exceeds MAXVALUE 无法实例化
SQL> create sequence TEST_SEQ
2 minvalue 1
3 maxvalue 200
4 start with 100
5 increment by 50
6 nocache
7 cycle;
Sequence created
SQL> SELECT TEST_SEQ.NEXTVAL FROM DUAL;
NEXTVAL
----------
100
SQL> SELECT TEST_SEQ.NEXTVAL FROM DUAL;
NEXTVAL
----------
150
SQL> SELECT TEST_SEQ.NEXTVAL FROM DUAL;
NEXTVAL
----------
200
在达到了sequenc的最大值后,如果设置了循环cycle,sequence的初始值将从minvalue开始,而不是设置的start with 值。
SQL> SELECT TEST_SEQ.NEXTVAL FROM DUAL;
NEXTVAL
----------
1
SQL> SELECT TEST_SEQ.NEXTVAL FROM DUAL;
NEXTVAL
----------
51
SQL> SELECT TEST_SEQ.NEXTVAL FROM DUAL;
NEXTVAL
----------
101
SQL> SELECT TEST_SEQ.NEXTVAL FROM DUAL;
NEXTVAL
----------
151
SQL> SELECT TEST_SEQ.NEXTVAL FROM DUAL;
NEXTVAL
----------
1
SQL> SELECT TEST_SEQ.NEXTVAL FROM DUAL;
NEXTVAL
----------
51