Sequence pseudocolumns

A sequence is a set of incrementing numbers generated by the database according to a certain rule. Because of its incrementing feature, a sequence is often used as a primary key or unique key. This topic describes how to use sequences, the scenarios where sequences can be used, and the considerations.

How to use sequences

You can use the following pseudocolumns to reference the values of a sequence in an SQL statement:

• CURRVAL: returns the current value of the sequence.

• NEXTVAL: returns the next incrementing value of the sequence.

When you use the CURRVAL or NEXTVAL pseudocolumns, you must specify the name of the sequence before the pseudocolumns and use a period (.) to separate the sequence name and pseudocolumns. For example, if the sequence name is SEQ_FOO, you can use SEQ_FOO.CURRVAL to obtain the current value of the SEQ_FOO sequence. You can also use SEQ_FOO.NEXTVAL to obtain the next incrementing value of the SEQ_FOO sequence.

Scenarios where sequences can be used

The values of the CURRVAL and NEXTVAL pseudocolumns can be used in the following scenarios:

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

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

• The VALUE clause of an INSERT statement.

• The SET clause of an UPDATE statement.

The values of the CURRVAL and NEXTVAL pseudocolumns cannot be used in the following scenarios:

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

• A query on a view.

• A SELECT statement that contains the DISTINCT operator.

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

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

• The WHERE clause of a SELECT statement.

• The condition of a CHECK constraint.

Considerations

When you create a sequence, you must specify its initial value and increment. The first time you reference NEXTVAL, the initial value of the sequence is returned. Subsequent references to NEXTVAL return a new value that is the previous value of the sequence plus the increment. 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 the increment between values. The first time you reference NEXTVAL, the initial value of the sequence is returned. Subsequent references to NEXTVAL return a new value that is the previous value of the sequence plus the increment. 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:

• The sequence is incremented once for each row returned by the outer query block of a SELECT statement. This type of query block can appear in the following locations:

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

• The sequence is incremented once for each row updated by an UPDATE statement.

• The sequence is incremented once for each INSERT statement that contains a VALUES clause.

  Notice

  Starting from OceanBase Database V4.3.1, the behavior of sequence incrementing has changed. In INSERT statements, 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, if you do not reference the sequence value), the sequence value remains unchanged.

• The sequence is incremented once for each row merged by a MERGE statement. NEXTVAL can appear in the merge_insert_clause or merge_update_clause clause, or both. NEXTVAL is incremented for each row update and insert, 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 once for each row, and all NEXTVAL instances in the row return the same value.

If a sequence's NEXTVAL is referenced multiple times in these scenarios, the sequence is incremented only once, and all referenced NEXTVAL instances return the next sequence value.

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

A sequence can be accessed by multiple users simultaneously without waiting or locking. Here is an example of how to query a sequence:

SELECT SEQUENCE_NAME.NEXTVAL FROM DUAL;      /*The sequence number is incremented each time it is executed.*/
SELECT SEQUENCE_NAME.CURRVAL FROM DUAL;     /*The sequence number remains unchanged after multiple executions.*/