NEXTVAL and CURRVAL Operators

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 object that exists in the same database, using 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 statement.

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.