Sequence pseudo-column

A sequence is a set of auto-incremented numbers generated in a database according to a specific rule. Because of its auto-increment feature, a sequence is commonly used as a primary key or unique key. This topic describes how to obtain the values of a sequence, the applicable scenarios, and the usage notes.

Obtain the values of a sequence

You can use the following pseudo-columns to obtain the values of a sequence in an SQL statement:

• CURRVAL: returns the current value of a sequence.

• NEXTVAL: returns the next auto-incremented value of a sequence.

When you use the CURRVAL or NEXTVAL pseudo-column, you must specify the name of the sequence before the pseudo-column and separate them with a period (.). For example, if the name of the sequence is SEQ_FOO, you can use SEQ_FOO.CURRVAL to obtain the current value of the SEQ_FOO sequence. Similarly, you can use SEQ_FOO.NEXTVAL to obtain the next auto-incremented value of the SEQ_FOO sequence.

Applicable scenarios

The values obtained by using CURRVAL and NEXTVAL can be used in the following scenarios:

• The SELECT list of a SELECT statement that is not a subquery or a view.

• The SELECT list of a subquery in an INSERT statement.

• The VALUE clause of an INSERT statement.

• The SET clause of an UPDATE statement.

The values obtained by using CURRVAL and NEXTVAL cannot be used in the following scenarios:

• A subquery in a DELETE, SELECT, or UPDATE statement.

• A query on a view.

• A SELECT statement that contains the DISTINCT operator.

• A SELECT statement that contains the GROUP BY or ORDER BY clause.

• A SELECT statement that contains the UNION, INTERSECT, or MINUS operator.

• The WHERE clause of a SELECT statement.

• The condition of a CHECK constraint.

Usage notes

When you create a sequence, you must specify its initial value and increment. The first time you use NEXTVAL, it returns the initial value of the sequence. Subsequent uses of NEXTVAL return new values by adding the increment to the previous value. Any use of CURRVAL returns the current value of the sequence, which is the value returned by the last use of NEXTVAL.

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

When you create a sequence, you can specify its initial value and increment. The first time you use NEXTVAL, it returns the initial value of the sequence. Subsequent uses of NEXTVAL return new values by adding the increment to the previous value. Any use of CURRVAL returns the current value of the sequence, which is the value returned by the last use of NEXTVAL. For more information about how to create a sequence, see CREATE SEQUENCE.

When you use NEXTVAL in a single SQL statement, OceanBase Database increments the sequence as follows:

• The sequence is incremented once for each row returned by the outer query block of a SELECT statement. These query blocks can appear in the following locations:

  • The top-level SELECT statement.
  • The INSERT... SELECT statement. For multi-table insert operations, NEXTVAL must be in the VALUES clause. The sequence is incremented once for each row returned by a subquery, even if multiple branches reference NEXTVAL.
  • The CREATE TABLE ... AS SELECT statement.
  • The CREATE MATERIALIZED VIEW ... AS SELECT statement.

• The sequence is incremented once for each row updated by an UPDATE statement.

• The sequence is incremented once for each INSERT statement that contains the VALUES clause.

  Notice

  Starting from OceanBase Database V4.3.1, the behavior of sequence increment has changed. In an INSERT statement, the sequence value is incremented each time it is referenced. If you manually insert a value into a column that references a sequence, the sequence value remains unchanged.

• The sequence is incremented once for each row merged by a MERGE statement. NEXTVAL can appear in the merge_insert_clause or merge_update_clause clause, or both. NEXTVAL is incremented for each row update and insert, even if the sequence value is not used in the update or insert operation. If NEXTVAL is specified multiple times in these clauses, the sequence is incremented once for each row, and all NEXTVAL references in the row return the same value.

If a sequence's NEXTVAL is referenced multiple times in these scenarios, the sequence is incremented once, and all referenced NEXTVAL references return the next value of the sequence.

If a sequence's CURRVAL and NEXTVAL are referenced at the same time in these scenarios, OceanBase Database increments the sequence, and both CURRVAL and NEXTVAL return the next value of the sequence.

Multiple users can access a sequence simultaneously without waiting or locking. Here is an example of how to query a sequence:

SELECT SEQUENCE_NAME.NEXTVAL FROM DUAL;      /*The sequence number increases each time it is executed.*/
SELECT SEQUENCE_NAME.CURRVAL FROM DUAL;     /*The sequence number remains unchanged after it is executed multiple times.*/