Sequence pseudocolumns

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

Value setting method of sequences

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 value of the sequence.

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 the CURRVAL and NEXTVAL pseudocolumns can be used in the following positions:

• The select list of a SELECT statement that is not contained in a subquery, materialized view, 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 of a view or a materialized 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.

Usage notes

When you create a sequence, you need to define its initial 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 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 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, 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:

• 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. 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 places, 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. Statements in which a single sequence is queried:

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