About sequences

A sequence is a group of auto-increment serial numbers generated by the database based on certain rules, typically a group of numeric values (numbers) with an equal interval. A sequence is often used as the primary key or unique key.

Reference a sequence

You can use the following pseudocolumns to reference sequence values in SQL statements:

• CURRVAL: returns the current value of the sequence.

• NEXTVAL: returns the next incremental value.

To use a sequence pseudocolumn, you must qualify CURRVAL and NEXTVAL with the name of the sequence followed by a period (.).

For example, if the name of the sequence is SEQ_FOO, you can use SEQ_FOO.CURRVAL to query the current value of the SEQ_FOO sequence. Likewise, you can use SEQ_FOO.NEXTVAL to query the next value of the SEQ_FOO sequence.

Use a sequence

Sequence values referenced by using CURRVAL and NEXTVAL can be used in the following positions:

• The select list of a SELECT statement.

• The VALUES clause of an INSERT statement.

• The SET clause of an UPDATE statement.

Examples of using NEXTVAL and CURRVAL to obtain a sequence value:

SELECT SEQUENCE_NAME.NEXTVAL FROM DUAL;      /*The sequence value increments each time the statement is executed.*/
SELECT SEQUENCE_NAME.CURRVAL FROM DUAL;     /*The sequence value does not change no matter how many times the statement is executed.*/

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

Before you refer to CURRVAL of a sequence in the session, you must use NEXTVAL to initialize the sequence values first.

When you create a sequence, you can define its initial value and the step size 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 step size and return a new value. Any reference to CURRVAL always returns the current value of the sequence, which is the value returned by the last reference to NEXTVAL.

Multiple references to NEXTVAL in the same row will return the same value.

obclient> SELECT seq1.NEXTVAL, seq1.NEXTVAL FROM DUAL;
+---------+---------+
| NEXTVAL | NEXTVAL |
+---------+---------+
|       3 |       3 |
+---------+---------+
1 row in set

Simultaneous references to NEXTVAL and CURRVAL in the same row will also return the same value.

obclient> SELECT seq1.NEXTVAL, seq1.CURRVAL FROM DUAL;
+---------+---------+
| NEXTVAL | CURRVAL |
+---------+---------+
|       4 |       4 |
+---------+---------+
1 row in set