Managing Sequences

A sequence is a database object that generates unique sequential values. These values are often used for primary and unique keys. Using a sequence generator to provide the value for a primary key in a table guarantees that the key value is unique.

You can refer to sequence values in SQL statements with these pseudocolumns:

CURRVAL: Returns the current value of a sequence
NEXTVAL: Increments the sequence and returns the next value

You must qualify CURRVAL and NEXTVAL with the name of the sequence, such as employees_seq.CURRVAL or employees_seq.NEXTVAL.

When you create a sequence, you can define its initial value and the increment between its values. The first reference to NEXTVAL returns the initial value of the sequence. Subsequent references to NEXTVAL increment the sequence value by the defined increment and return the new value. Any reference to CURRVAL returns the current value of the sequence, which is the value returned by the last reference to NEXTVAL.

Before you use CURRVAL for a sequence in your session, you must first initialize the sequence with NEXTVAL.

This section contains the following topics:

For examples of managing sequences using SQL statements, see "Creating and Dropping a Sequence With SQL".