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 value setting method, application scenarios, and usage notes of sequences.

Reference a sequence

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.

• The 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 initial value and increment. The first reference to NEXTVAL returns the initial value of the sequence. Subsequent references to NEXTVAL increment the sequence value by the defined increment 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 refer to 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 increment between two values. The first reference to NEXTVAL returns the initial value of the sequence. Subsequent references to NEXTVAL increment the sequence value by the defined increment 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, OceanBase Database increments the sequence in the following way:

• 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 is incremented 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 is incremented once for each row updated in an UPDATE statement.

• The sequence is incremented for each INSERT statement containing a VALUES clause.

• The sequence is incremented for each row merged by a MERGE statement. NEXTVAL can be referenced in either or both of the merge_insert_clause or merge_update_clause clause. The NEXTVAL value is incremented 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 is incremented 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 is incremented 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 number 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.*/