Creation and use of Oracle sequence

Creation of an Oracle sequence
CREATE SEQUENCE name
[br>]
[START WITH n]
[{MAXVALUE n>5 NOMAXVALUE}]0
1 2 [{MINVALUE n>6 NOMINVALUE}]3
4 [{CYCLE|NOCYCLE}]
[{CACHE n| NOCACHE}];
Parameter description:
INCREMENT BY– the step of a sequence change, that is, the step of the sequence, defaults to 1; Negative values indicate that the value of this Oracle sequence is decreasing in this step.
START WITH– – the initial value of the sequence, default is 1.
MAXVALUE – – the maximum that can be generated by the sequence. (default does not limit maximum: NOMAXVALUE — for increasing Oracle sequences, the maximum the system can produce is 10 to the 27th power; For descending sequences, the maximum value is -1)
MINVALUE – – the minimum value that can be generated by the sequence. (default does not limit minimum: NOMINVALUE)
CYCLE – – used to define whether a CYCLE (NOCYCLE: NOCYCLE, CYCLE: CYCLE) will occur when the value produced by the sequence reaches the limit value.
CACHE – – represents the number of cached sequences. Abnormal termination of the database may cause the sequence to be interrupted and discontinuous. The default value is 20.
Example:

CREATE SEQUENCE SEQ_DEMO INCREMENT BY 1 START WITH 1 NOMAXVALUE NOCYCLE NOCACHE

Use of Oracle sequences
currval – represents the current value of the sequence, the new sequence must be used once nextval to obtain the value, otherwise an error will be reported
nextval – represents the next value of the sequence. The first time a new sequence is used, the initial value of the sequence is obtained, and the set step increments start from the second use
The value of the query sequence:
select seq_name.[currval/nextval] seqno from dual;

1) dual : is a virtual table of oracle, not real.
2) seq_name : is the name given by the developer as a “sequence”, which is usually used to generate id Numbers.
3) seq_name.nextval : takes the next value of the sequence. If the current value of the sequence is 100, execute the above SELECT statement and seqNO becomes 101. One more time, seqno will get to 102… …

Conclusion:
To implement id autoincrement, Oracle needs to use sequence implementation. nextval must be called to generate a sequence value before using currval to see the current value. The starting value of the sequence must not be less than the minimum value; To create a loop sequence, the maximum value must be set; If a cached sequence is created, the cached value must satisfy the constraint formula: Max - min > =(cache value -1)* the value of each loop .


Read More: