Sequence pseudocolumns

A sequence is a set of auto-incrementing numbers generated by a database according to a specific rule. Because of the auto-increment feature, sequences are commonly used as primary keys and unique keys. This topic explains how to use sequence pseudocolumns, their advantages, and scenarios.

Methods for obtaining sequence values

You can execute the following SQL statements to obtain sequence values:

• CURRVAL: returns the current value of a sequence.

• NEXTVAL: returns the next sequential value of a sequence.

When you use sequence pseudocolumns, you must prefix the pseudocolumn name with the sequence name 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 sequential value by using SEQ_FOO.NEXTVAL.

Scenarios for 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 the values are generated by a subquery.

• 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 CHECK constraint.

• The WHERE clause of a DELETE, SELECT, or UPDATE statement.

• The select list of a view.

• The select list of a SELECT statement that contains the DISTINCT operator.

• The select list of a SELECT statement with the GROUP BY or ORDER BY clause.

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

• The CHECK constraint condition.

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 remains unchanged and 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 of the sequence. 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.

The database increments a sequence in the following ways when you reference NEXTVAL in an SQL statement:

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

  • At the top level.
  • In an INSERT... SELECT statement. For multi-table insert operations, the NEXTVAL expression must be placed in the VALUES clause. The database increments the sequence each time a row is returned by a subquery in the VALUES clause, even if multiple branches reference the NEXTVAL expression.
  • In a CREATE TABLE ... AS SELECT statement.
  • In a CREATE MATERIALIZED VIEW ... AS SELECT statement.

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

  Notice

  In OceanBase Database V4.2.1, from build package (BP) V4.2.1 BP8 onwards, the behavior of incrementing a sequence in an INSERT statement has been changed to: Each time the sequence is referenced in an INSERT statement, its value is incremented. If a value is manually inserted into a column that references the sequence (namely, the sequence value is not referenced in the column), the sequence value remains unchanged.

• The database increments the sequence each time a row is merged in a MERGE statement. The NEXTVAL expression can appear in the merge_insert_clause, merge_update_clause, or both clauses. The database increments the sequence each time a row is updated or inserted, even if the sequence value is not used in the update or insert operation. If the NEXTVAL expression is specified multiple times in these positions, the database increments the sequence each time a row is updated or inserted, and all NEXTVAL expressions 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, the NEXTVAL expressions in all references return the next sequence value.

If both the CURRVAL and NEXTVAL of a sequence are referenced at the same time, the database increments the sequence, namely, the CURRVAL and NEXTVAL expressions return the next sequence value.

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

SELECT SEQUENCE_NAME.NEXTVAL FROM DUAL;      /*Each time the sequence number is queried, it is incremented.*/
SELECT SEQUENCE_NAME.CURRVAL FROM DUAL;     /*The sequence number does not change.*/