Sequence pseudocolumns

A sequence is an auto-increment sequence generated by the database based on certain rules and is usually a group of numeric values at equal intervals. Because of its auto-increment values, a sequence is often used as a primary key and a unique key.

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.

Usage notes

Sequence values referenced by using the CURRVAL and NEXTVAL pseudocolumns can be used in the following positions:

• In the select list of a top-level SELECT statement

• In the VALUES clause of an INSERT statement

• In the SET clause of an UPDATE statement

The following shows an example of using a sequence independently:

SELECT SEQUENCE_NAME.NEXTVAL FROM DUAL;      /*The sequence number increments each time this statement is executed.*/

SELECT SEQUENCE_NAME.CURRVAL FROM DUAL;     /*The sequence number remains the same no matter how many times this statement is executed.*/

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, modify, and drop a sequence, see CREATE SEQUENCE, ALTER SEQUENCE, and DROP SEQUENCE.

Examples

obclient> CREATE SEQUENCE s1 START WITH 95 INCREMENT BY 1 NOORDER CACHE 10000;
Query OK, 0 rows affected

obclient> CREATE TABLE tbl1 (i INT,j INT);
Query OK, 0 rows affected

obclient> INSERT INTO tbl1 VALUES(1,70),(2,71),(3,3),(4,4);
4 rows affected

obclient> SELECT * FROM tbl1;
+---+------+
| I | J    |
+---+------+
| 1 |   70 |
| 2 |   71 |
| 3 |    3 |
| 4 |    4 |
+---+------+
4 rows in set 

obclient> SELECT s1.nextval, i, j FROM tbl1;
+------------+---+------+
| S1.NEXTVAL | I | J    |
+------------+---+------+
|         95 | 1 |   70 |
|         96 | 2 |   71 |
|         97 | 3 |    3 |
|         98 | 4 |    4 |
+------------+---+------+
4 rows in set

obclient> SELECT s1.nextval, i, j FROM tbl1;
+------------+---+------+
| S1.NEXTVAL | I | J    |
+------------+---+------+
|        99  | 1 |   70 |
|        100 | 2 |   71 |
|        101 | 3 |    3 |
|        102 | 4 |    4 |
+------------+---+------+
4 rows in set

obclient> UPDATE tbl1 SET i = s1.nextval;
Query OK, 4 rows affected
Rows matched: 4  Changed: 4  Warnings: 0

obclient> SELECT * FROM tbl1;
+-----+------+
| I   | J    |
+-----+------+
| 103 |   70 |
| 104 |   71 |
| 105 |    3 |
| 106 |    4 |
+-----+------+
4 rows in set 

obclient> SELECT s1.currval FROM DUAL;
+---------+
| currval |
+---------+
| 106     | 
+---------+
1 row in set