Sequences

A sequence is a database object , which can generate unique., sequential integer values.it can be used to automatically generate primary key or unique key values.

Create Sequence [sequence_name]Increment by [no]Start with [no[Maxvalue [no]Minvalue [no] Cycle cache [no]

1.Increment by no
No is an integer which specifies the interval between sequence numbers.

2.Start with no
It spcifies the first sequence number to be generatedminvalue no - specifiesthe minimum value of the sequence.

3. Maxvalue no
Specifies the maximum value of the sequence.

4.Cycle
Specifies that the sequence continues to generate values from the beginning after reaching either its max or min value.

5.Nocycle
Specifies that the sequence cannot generate more values after reaching either its maxvalue or minvalue.the default value is ' no cycle'.

6.Cache
The cache option pre-allocate a set of sequence number and retains them in memory so that sequence numbers can be accessed faster.

7.Nextval
Nextval returns initial value of the sequence , when referred to for the first time , later references to nextval will increment the sequence using the increment by clause and return the new value.

8.Curval
Curval returns the current value of the sequence which is the value returned by the last referenced to nextval.

Alter Sequence Procedure

The sequence can be altered when we want to perform the following :

a) Set ( or ) reduce minvalue or maxvlue.

b) Change the increment value.

c) Change the number of cached sequence numbers.

Data dictionary table for sequence User_Sequences will provide details of sequences that the user has created.