Joey Javas

Sequences are used in Oracle as an autonumber field. Sequences can be created using the following syntax:

CREATE SEQUENCE sequence_name
START WITH value
MAXVALUE value
MINVALUE value
INCREMENT BY value
CACHE value;

The parameters are defined as followed:

START WITH - The integer value at which the sequence begins. (Default is 1.)
MAXVALUE – The highest value to which the sequence can be incremented. (Default is 999999999999999999999999999.)
MINVALUE – The lowest value to which the sequence can be decremented. (Default is -999999999999999999999999999.)
INCREMENT BY - The integer value by which the sequence should incremented (use a negative integer to decrement). (Default is 1.)
CACHE – The number of sequence values to keep in memory, in order to improve performance. Note that if a grouping of sequence numbers are put into memory and then, due to system issues, are never put into use that those sequences are no longer available. This may cause gaps in the sequence. (NOCACHE can also be used and is the default.)

To retrieve the current value of a sequence, use CURRVAL, as in:

SELECT sequence_name.CURRVAL

To retrieve the next value of a sequence, use NEXTVAL, as in:

SELECT sequence_name.NEXTVAL

This also increments the sequence. It is useful when it is necessary to incremented the sequence as part of an insert statement, as follows:

INSERT INTO products(product_id, product_name)
VALUES (product_seq.nextval, ‘widget’);