Sequence pseudocolumns

A sequence is a set of auto-incremented numbers generated by a database according to a specific rule. Because of its auto-incremented feature, it is often used as a primary key or a unique key. This topic describes how to obtain sequence values, the applicable scenarios, and the usage notes.

How to obtain sequence values

You can use the following pseudocolumns to obtain sequence values in an SQL statement:

• CURRVAL: returns the current value of a sequence.

• NEXTVAL: returns the next auto-incremented value of a sequence.

When you use the CURRVAL or NEXTVAL pseudocolumn, you must specify the name of the sequence before the pseudocolumn and separate them with a period (.) to reference the pseudocolumn. For example, if the name of the sequence is SEQ_FOO, you can use SEQ_FOO.CURRVAL to obtain the current value of the SEQ_FOO sequence. Similarly, you can use SEQ_FOO.NEXTVAL to obtain the next auto-incremented value of the SEQ_FOO sequence.

Applicable scenarios

You can use the values obtained by CURRVAL and NEXTVAL 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 VALUES clause of an INSERT statement.

• In the SET clause of an UPDATE statement.

However, you cannot use the values obtained by CURRVAL and NEXTVAL in the following scenarios:

• In a subquery of a DELETE, SELECT, or UPDATE statement.

• In a query of a view.

• In a SELECT statement that contains the DISTINCT operator.

• In a SELECT statement that contains the GROUP BY or ORDER BY clause.

• In a SELECT statement that combines with another SELECT statement by using the UNION, INTERSECT, or MINUS operator.

• In the WHERE clause of a SELECT statement.

• In the condition of a CHECK constraint.

Usage notes

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 that is the previous value of the sequence plus the increment specified when the sequence was created. Any reference to CURRVAL returns the current value of the sequence, which is the value returned by the last reference to NEXTVAL.

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

When you create a sequence, you can 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 that is the previous value of the sequence plus the increment specified when the sequence was created. Any reference to CURRVAL 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 as follows:

• Each time a row is returned by the outer query block of a SELECT statement, the sequence is incremented. The outer query block can be in the following locations:

  • The top-level SELECT statement.
  • The INSERT... SELECT statement. For multi-table insert operations, NEXTVAL must be in the VALUES clause. The sequence is incremented each time a row is returned by a subquery, regardless of how many branches reference NEXTVAL.
  • The CREATE TABLE ... AS SELECT statement.
  • The CREATE MATERIALIZED VIEW ... AS SELECT statement.

• Each time a row is updated by an UPDATE statement, the sequence is incremented.

• Each time an INSERT statement contains the VALUES clause, the sequence is incremented.

  Notice

  Starting from OceanBase Database V4.3.1, the behavior of sequence increment has changed. In an INSERT statement, the sequence value is incremented each time it is referenced. If you manually insert a value into a column that references a sequence (that is, the sequence value is not referenced), the sequence value remains unchanged.

• Each time a row is merged by a MERGE statement, the sequence is incremented. NEXTVAL can be in the merge_insert_clause or merge_update_clause clause, or both. NEXTVAL is incremented for each update and insert operation, 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 in these clauses, the sequence is incremented only once, and all referenced NEXTVAL return the next value of the sequence.

If a sequence's CURRVAL and NEXTVAL are referenced at the same time in these clauses, OceanBase Database increments the sequence, and both CURRVAL and NEXTVAL return the next value of the sequence.

A sequence can be accessed by multiple users at the same time 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 does not change even if it is executed multiple times.*/