NEXTVAL and CURRVAL Operators (IDS)

You can access the value of a sequence using the NEXTVAL or CURRVAL operators in SQL statements. You must qualify NEXTVAL or CURRVAL with the name (or synonym) of a sequence that resides on the same database in the format sequence.NEXTVAL or sequence.CURRVAL. An expression can also qualify sequence by the owner name, as in zelaine.myseq.CURRVAL. You can specify the SQL identifier of sequence or a valid synonym, if one exists.

In an ANSI-compliant database, you must qualify the name of the sequence with the name of its owner (owner.sequence) if you are not the owner.

To use NEXTVAL or CURRVAL with a sequence, you must have the Select privilege on the sequence or have the DBA privilege on the database. For information about sequence-level privileges, see the GRANT statement.

Using NEXTVAL

To access a sequence for the first time, you must refer to sequence.NEXTVAL before you can refer to sequence.CURRVAL. The first reference to NEXTVAL returns the initial value of the sequence. Each subsequent reference to NEXTVAL increments the value of the sequence by the defined step and returns a new incremented value of the sequence.

You can increment a given sequence only once within a single SQL statement. Even if you specify sequence.NEXTVAL more than once within a single statement, the sequence is incremented only once, so that every occurrence of sequence.NEXTVAL in the same SQL statement returns the same value.

Except for the case of multiple occurrences within the same statement, every sequence.NEXTVAL expression increments the sequence, regardless of whether you subsequently commit or roll back the current transaction.

If you specify sequence.NEXTVAL in a transaction that is ultimately rolled back, some sequence numbers might be skipped.

Using CURRVAL

Any reference to CURRVAL returns the current value of the specified sequence, which is the value that your last reference to NEXTVAL returned. After you generate a new value with NEXTVAL, you can continue to access that value using CURRVAL, regardless of whether another user increments the sequence.

If both sequence.CURRVAL and sequence.NEXTVAL occur in an SQL statement, the sequence is incremented only once. In this case, each sequence.CURRVAL and sequence.NEXTVAL expression returns the same value, regardless of the order of sequence.CURRVAL and sequence.NEXTVAL within the statement.

Concurrent Access to a Sequence

A sequence always generates unique values within a database without perceptible waiting or locking, even when multiple users refer to the same sequence concurrently. When multiple users use NEXTVAL to increment the sequence, each user generates a unique value that other users cannot see.

When multiple users concurrently increment the same sequence, gaps occur between the values that each user sees. For example, one user might generate a series of values, such as 1, 4, 6, and 8, from a sequence, while another user concurrently generates the values 2, 3, 5, and 7 from the same sequence object.

Restrictions

NEXTVAL and CURRVAL are valid only in SQL statements, not directly in SPL statements. (But SQL statements that use NEXTVAL and CURRVAL can be used in SPL routines.) The following restrictions apply to these operators in SQL statements:

• You must have Select privilege on the sequence.
• In a CREATE TABLE or ALTER TABLE statement, you cannot specify NEXTVAL or CURRVAL in the following contexts:
  • In the Default clause
  • In the definition of a check constraint.
• In a SELECT statement, you cannot specify NEXTVAL or CURRVAL in the following contexts:
  • In the projection list when the DISTINCT keyword is used
  • In the WHERE, GROUP BY, or ORDER BY clauses
  • In a subquery
  • When the UNION operator combines SELECT statements.
• You also cannot specify NEXTVAL or CURRVAL in these contexts:
  • In fragmentation expressions
  • In reference to a remote sequence object in another database.

Examples

In the following examples, it is assumed that no other user is concurrently accessing the sequence and that the user executes the statements consecutively.

These examples are based on the following sequence object and table:

CREATE SEQUENCE seq_2 
   INCREMENT BY 1 START WITH 1 
   MAXVALUE 30 MINVALUE 0 
   NOCYCLE CACHE 10 ORDER;

CREATE TABLE tab1 (col1 int, col2 int);
INSERT INTO tab1 VALUES (0, 0);

You can use NEXTVAL (or CURRVAL) in the Values clause of an INSERT statement, as the following example shows:

INSERT INTO tab1 (col1, col2) 
   VALUES (seq_2.NEXTVAL, seq_2.NEXTVAL)

In the previous example, the database server inserts an incremented value (or the first value of the sequence, which is 1) into the col1 and col2 columns of the table.

You can use NEXTVAL (or CURRVAL) in the SET clause of the UPDATE statement, as the following example shows:

UPDATE tab1 
   SET col2 = seq_2.NEXTVAL
   WHERE col1 = 1;

In the previous example, the incremented value of the seq_2 sequence, which is 2, replaces the value in col2 where col1 is equal to 1.

The following example shows how you can use NEXTVAL and CURRVAL in the Projection clause of the SELECT statement:

SELECT seq_2.CURRVAL, seq_2.NEXTVAL FROM tab1;

In the previous example, the database server returns two rows of incremented values, 3 and 4, from both the CURRVAL and NEXTVAL expressions. For the first row of tab1, the database server returns the incremented value 3 for CURRVAL and NEXTVAL; for the second row of tab1, it returns the incremented value 4.

For more examples on how to use NEXTVAL and CURRVAL, see the IBM Informix Guide to SQL: Tutorial.