Sequence pseudocolumns

A sequence is a set of automatically generated, sequential numbers based on a specified rule in a database. Because of the auto-increment feature, 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 contains a sequence reference to obtain a sequence value:

• CURRVAL: returns the current value of a sequence.

• NEXTVAL: returns the next 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 value of the SEQ_FOO sequence by using SEQ_FOO.NEXTVAL.

Scenarios for using sequence values

You can use sequence values obtained by referencing CURRVAL or 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 that contains a subquery.

• The VALUE clause of an INSERT statement.

• The SET clause of an UPDATE statement.

Sequence values obtained by referencing CURRVAL or NEXTVAL cannot be used in the following scenarios:

• The condition of a DELETE, SELECT, or UPDATE statement or a subquery thereof.

• The query of a view.

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

• The select list of a SELECT statement that contains 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 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 increment. The first reference to NEXTVAL returns the initial value of the sequence. Subsequent references to NEXTVAL return the value obtained by adding the increment to the value returned in the previous reference. A reference to CURRVAL at any time returns the current value of the sequence, namely, the value returned by the last reference to NEXTVAL.

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

When you create a sequence, you can specify its initial value and increment. The first reference to NEXTVAL returns the initial value of the sequence. Subsequent references to NEXTVAL return the value obtained by adding the increment to the value returned in the previous reference. A 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 a sequence each time the NEXTVAL pseudocolumn is referenced, as follows:

• 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:

  • As the outermost SELECT statement.
  • In an INSERT... SELECT statement. For multi-table insert operations, NEXTVAL must appear in the VALUES clause. The sequence is incremented each time a row is 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.

• In an UPDATE statement, the sequence is incremented each time a row is updated.

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

  Notice

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

• In a MERGE statement, the sequence is incremented each time a row is merged. NEXTVAL can appear in the merge_insert_clause, merge_update_clause, or both clauses. The sequence is incremented each time a row is 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 for each row, and all NEXTVAL references in the row return the same value.

If a sequence's NEXTVAL is referenced multiple times at the same location, the sequence is incremented only once, namely, the NEXTVAL referenced the first time is incremented, and all subsequent NEXTVAL references return the same next sequence value.

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

Sequences can be accessed by multiple users simultaneously without waits or locks. Here is an example of querying a sequence:

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