Sequence pseudocolumns

A sequence is an auto-increment sequence generated by the database based on predefined rules. A sequence is often used as the primary key or unique key. This topic describes the sequence value reference method, application scenarios, and usage notes of sequences.

Reference sequence values

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

• CURRVAL: returns the current value of the sequence.

• NEXTVAL: returns the next incremental value.

To use a sequence pseudocolumn, you must qualify CURRVAL and NEXTVAL with the name of the sequence followed by a period (.). For example, if the name of the sequence is SEQ_FOO, you can use SEQ_FOO.CURRVAL to query the current value of the SEQ_FOO sequence. Likewise, you can use SEQ_FOO.NEXTVAL to query the next value of the SEQ_FOO sequence.

Application scenarios

Sequence values referenced by using CURRVAL and NEXTVAL can be used in the following positions:

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

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

• The VALUES clause of an INSERT statement.

• The SET clause of an UPDATE statement.

Values referenced by using CURRVAL and NEXTVAL cannot be used in the following positions:

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

• A query for a view.

• A SELECT statement with a DISTINCT operator.

• A SELECT statement with a GROUP BY clause or an ORDER BY clause.

• A SELECT statement that is combined with another SELECT statement by using the UNION, INTERSECT, or MINUS set operator.

• A WHERE clause of a SELECT statement.

• The DEFAULT value of a column in a CREATE TABLE or ALTER TABLE statement.

• The condition of a CHECK constraint.

Considerations

When you create a sequence, you need to define its start value and step size. The first reference to NEXTVAL returns the initial value of the sequence. Subsequent references to NEXTVAL increment the sequence value by the defined step size and return a new value. Any reference to CURRVAL always returns the current value of the sequence, which is the value returned by the last reference to NEXTVAL.

Before you reference CURRVAL of a sequence in the session, you must use NEXTVAL to initialize the sequence values first.

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

When you reference NEXTVAL in a single SQL statement, the sequence increments in the following way in OceanBase Database:

• It increments once for each row returned by the outer query block of a SELECT statement. Such a query block can appear in the following positions:

  • A top-level SELECT statement.
  • An INSERT... SELECT statement For a multi-table insert, you must reference NEXTVAL in the VALUES clause, and the sequence increments once for each row returned by the subquery, even if you reference NEXTVAL in multiple branches of the insert.
  • A CREATE TABLE ... AS SELECT statement.
  • A CREATE MATERIALIZED VIEW ... AS SELECT statement.

• The sequence increments once for each INSERT statement containing a VALUES clause.

  Notice

  For OceanBase Database V4.2.x, starting from V4.2.4, the behavior of sequence increment has changed. In an INSERT statement, the sequence value increments each time it is referenced. If a value is manually inserted into a column that references the sequence (that is, the sequence value is not used), the sequence value remains unchanged.

• The sequence increments once for each row merged by a MERGE statement. NEXTVAL can be referenced in either or both of the merge_insert_clause and merge_update_clause clauses. The NEXTVAL value increments for each row updated or inserted, even if the sequence number is not used in the update or insert operation. If NEXTVAL is referenced more than once at these positions, the sequence increments once for each row and returns the same value for all appearances of NEXTVAL in that row.

When NEXTVAL is referenced more than once in these positions, the sequence increments only once and all references to NEXTVAL return the next value of the current sequence.

When CURRVAL and NEXTVAL are both referenced more than once in these positions, OceanBase Database increments the sequence, and all references to CURRVAL and NEXTVAL return the next value of the current sequence.

A sequence can be accessed by many users at the same time without waiting or locking. The following are sample statements in which a single sequence is queried:

SELECT SEQUENCE_NAME.NEXTVAL FROM DUAL;      /*The sequence increments each time the statement is executed.*/
SELECT SEQUENCE_NAME.CURRVAL FROM DUAL;     /*The sequence remains the same no matter how many times this statement is executed.*/