Sequence pseudocolumns

A sequence is a set of automatically generated, sequential numbers based on a specified rule in a database. Because numbers in a sequence are unique and incremental, sequences are commonly used as primary keys and unique keys. This topic explains how to use sequence pseudocolumns and provides information about their applications and considerations.

Methods for obtaining sequence values

You can execute an SQL statement that references a sequence in the following ways to obtain the sequence values:

• CURRVAL: returns the current value of the sequence.

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

When you reference a sequence using a pseudocolumn, you must prefix the sequence name to CURRVAL or NEXTVAL and separate them with a period (.). For example, if the sequence name is SEQ_FOO, you can obtain the current value of the SEQ_FOO sequence by using SEQ_FOO.CURRVAL, and the next auto-increment value of the SEQ_FOO sequence by using SEQ_FOO.NEXTVAL.

Scenarios for using sequence values

You can use sequence values obtained through CURRVAL and NEXTVAL in the following scenarios:

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

• The select list of an INSERT statement where a subquery is used.

• The VALUE clause of an INSERT statement.

• The SET clause of an UPDATE statement.

Sequence values obtained through CURRVAL and NEXTVAL cannot be used in the following scenarios:

• The condition of a DELETE, SELECT, or UPDATE statement where a subquery is used.

• The condition of a view.

• The select list of a SELECT statement where the DISTINCT operator is used.

• The select list of a SELECT statement where the GROUP BY or ORDER BY clause is used.

• The select list of a SELECT statement that is combined with another SELECT statement through the UNION, INTERSECT, or MINUS operator.

• The WHERE clause of a SELECT statement.

• The condition of a CHECK constraint.

Sequence notes

When you create a sequence, you must specify its initial value and step. The first reference to NEXTVAL returns the initial value of the sequence. References to NEXTVAL after that return a new value each time by adding the step to the value returned the last time. The value of CURRVAL always returns the current value of the sequence, namely, the value returned by the last reference to NEXTVAL.

Before you can reference the CURRVAL of a sequence in a session, you must initialize the sequence value in this session by referencing the NEXTVAL of the sequence.

When you create a sequence, you can specify its initial value and the incremental value. The first reference to NEXTVAL returns the initial value. References to NEXTVAL after that return a new value each time by adding the incremental value to the value returned the last time. Any reference to CURRVAL always returns the current value of the sequence, namely, the value returned by the last reference to NEXTVAL. For more information, see CREATE SEQUENCE.

In a single SQL statement, OceanBase Database increments the sequence each time the NEXTVAL pseudocolumn is referenced. The following rules apply:

• In an external query block of a SELECT statement, the sequence is incremented each time a row is returned. Such an external query block can appear in the following locations:

  • The top-level SELECT statement.
  • The INSERT... SELECT statement. In a multi-table insert operation, the NEXTVAL pseudocolumn must appear in the VALUES clause. The sequence is incremented each time a row is returned from the subquery, regardless of the number of branches that reference the NEXTVAL pseudocolumn.
  • The CREATE TABLE ... AS SELECT statement.
  • The CREATE MATERIALIZED VIEW ... AS SELECT statement.

• The sequence is incremented each time a row is updated in an UPDATE statement.

• The sequence is incremented each time an INSERT statement that contains the VALUES clause is executed.

  Notice

  In OceanBase Database V4.3.x, starting from V4.3.1, the behavior of sequence increment has been changed. In an INSERT statement, the value of the sequence is incremented each time the sequence is referenced. If a value is manually inserted into a column that references the sequence, the value of the sequence does not change.

• The sequence is incremented each time a row is merged in a MERGE statement. The NEXTVAL pseudocolumn can appear in the merge_insert_clause, merge_update_clause, or both. The sequence is incremented each time a row is updated or inserted, regardless of whether the sequence value is used in the update or insert operation. If the NEXTVAL pseudocolumn is specified multiple times in these positions, the sequence is incremented for each row, and all NEXTVAL pseudocolumns in the row return the same value.

If a sequence's NEXTVAL is referenced multiple times at the same position, the sequence is incremented only once, namely, it returns the next sequence value for all referenced NEXTVAL pseudocolumns.

If both the CURRVAL and NEXTVAL of a sequence are referenced at the same time, the sequence is incremented, namely, the referenced CURRVAL and NEXTVAL pseudocolumns return the next sequence value.

A sequence can be accessed by multiple users simultaneously without waiting or locking. Here is an example of querying a sequence:

SELECT SEQUENCE_NAME.NEXTVAL FROM DUAL;      /*The sequence number is incremented each time this statement is executed.*/
SELECT SEQUENCE_NAME.CURRVAL FROM DUAL;     /*The value of this statement does not change regardless of how many times it is executed.*/