What is SEQUENCE?

Sequence is a Schema or Database Object that can generate UNIQUE Sequential Values.

• The SEQUENCE values are often used for PRIMARY KEYS’s and UNIQUE KEY’S.
• Can be used to generate PRIMARY KEY values automatically.

Syntax:-

CREATE SEQUENCE 

INCREMENT BY 

START WITH 

MAXVAL 

MINVAL 

CYCYLE/NOCYCLE

CACHE /NOCACHE

ORDER/NOORDER

By default the sequence starts with 1, increments by 1 with minvalue of 1 and with nocycle, no cache.

SEQUENCE can be either incremented or decremented Sequence

INCREMENT BY: -

• Specifies the interval between the sequence numbers.
• Value can be positive or negative. But cannot be zero.
• If the value is positive it is Incremented sequence else it is decremented sequence.
• If omitted defaults to increment1 by 1.

MINVAL clause:-

• Specifies the sequences minimum value.

MAXVAL clause:-

• Specifies the maximum value that can be generated.

CYCLE clause:-

• Specifies the sequence will continue to generate values after reaching either maximum or minimum value.

NOCYCLE clause:-

• Specifies the sequence cannot generate more values after the targeted limit.

CACHE clause:-

• Specifies the pre-allocation of sequence numbers, the minimum is 2.

NOCACHE clause:-

• Specifies the values of sequence are not pre-allocated.

ORDER clause:-

• Guarantees the sequence number to be generated in the order of request.

NOORDER clause:-

• Does not guarantee the sequence number with order.

Example:-

CREATE SEQUENCE sampleseq

 INCREMENT BY 1

 START WITH 0

 MINVALUE 0

 MAXVALUE 10

 NOCACHE

 NOCYCLE;

--sequence created