Sequence pseudo columns

A sequence is a series of incrementing numbers generated by the database according to a specific rule. Due to its incrementing nature, sequences are commonly used as primary keys and unique keys. This topic describes how to retrieve sequence values, the scenarios where sequences can be used, and the considerations for using sequences.

How to retrieve sequence values

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

• CURRVAL: returns the current value of the sequence.

• NEXTVAL: returns the next incrementing value of the sequence.

When using sequence pseudo columns, you must specify the sequence name before CURRVAL and NEXTVAL, and separate them with a period (.). For example, if the sequence name is SEQ_FOO, you can use SEQ_FOO.CURRVAL to retrieve the current value of the SEQ_FOO sequence. Similarly, you can use SEQ_FOO.NEXTVAL to retrieve the next incrementing value of the SEQ_FOO sequence.

Scenarios for using sequence values

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

• In the select list of a SELECT statement that is not a subquery or a view.

• In the select list of a subquery in an INSERT statement.

• In the VALUE clause of an INSERT statement.

• In the SET clause of an UPDATE statement.

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

• In subqueries of DELETE, SELECT, or UPDATE statements.

• In queries of views.

• In SELECT statements that use the DISTINCT operator.

• In SELECT statements that use the GROUP BY or ORDER BY clause.

• In SELECT statements that use the UNION, INTERSECT, or MINUS set operators to combine with another SELECT statement.

• In the WHERE clause of a SELECT statement.

• In the conditions of CHECK constraints.

Considerations for using sequences

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

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

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

When you reference 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:

  • At the top level in a SELECT statement.
  • In an 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 the subquery, even if multiple branches reference NEXTVAL.
  • In a CREATE TABLE ... AS SELECT statement.
  • In a CREATE MATERIALIZED VIEW ... AS SELECT statement.

• The sequence is incremented once for each INSERT statement that includes a VALUES clause.

  Notice

  Starting from OceanBase Database V4.2.4, the behavior of sequence incrementing has changed. In INSERT statements, the sequence value is incremented each time it is referenced. If you manually insert a value into a column that references a sequence (i.e., without referencing the sequence value), the sequence value remains unchanged.

• The sequence is incremented once for each row merged in a MERGE statement. NEXTVAL can appear in the merge_insert_clause or merge_update_clause clause, or both. NEXTVAL is incremented for each row updated or inserted, 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.

When a sequence's NEXTVAL is referenced multiple times in these locations, the sequence is incremented only once, and all referenced NEXTVAL returns the next value of the sequence.

When a sequence's CURRVAL and NEXTVAL are referenced simultaneously in these locations, OceanBase Database increments the sequence, and both CURRVAL and NEXTVAL return the next value of the sequence.

Sequences can be accessed by multiple users 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 multiple executions.*/