Pseudo columns of sequences

A sequence is a set of incrementing numbers generated in a database by following a specific rule. It is usually a set of evenly spaced values of the numeric type. Because of the incrementing feature, a sequence is often used as a primary key or a unique key.

How to retrieve values from a sequence

You can retrieve values from a sequence by using the CURRVAL or NEXTVAL pseudo column.

• CURRVAL: returns the current value of the sequence.

• NEXTVAL: returns the next value of the sequence.

When you use a sequence, you must specify the sequence name before CURRVAL or NEXTVAL and separate them with a period (.) to reference the sequence.

For example, if the sequence name is SEQ_FOO, you can use SEQ_FOO.CURRVAL to retrieve the current value of the SEQ_FOO sequence. Similarly, you can use SEQ_FOO.NEXTVAL to retrieve the next value of the SEQ_FOO sequence.

Limitations

• You cannot use sequences with the HAVING, ORDER BY, or GROUP BY clause.
• You cannot use sequences in subqueries except for INSERT INTO SELECT statements.
• You cannot use sequences in WHERE expressions.

How to use sequences

The values of the CURRVAL and NEXTVAL pseudo columns can be used in the following contexts:

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

• In the VALUE clause of an INSERT statement.

• In the SET clause of an UPDATE statement.

Here is an example of a query that retrieves the value of a sequence:

SELECT SEQUENCE_NAME.NEXTVAL FROM DUAL;      /*The sequence number increases each time it is executed.*/

SELECT SEQUENCE_NAME.CURRVAL FROM DUAL;     /*The sequence number remains the same regardless of how many times it is executed.*/

When you create a sequence, you must specify its initial value and increment. The first time you reference NEXTVAL, it returns the initial value of the sequence. Subsequent references to NEXTVAL return a new value that is the previous value plus the defined 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 the CURRVAL pseudo column of a sequence in a session, you must first reference the NEXTVAL pseudo column of the sequence 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, it returns the initial value of the sequence. Subsequent references to NEXTVAL return a new value that is the previous value plus the defined 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 creating and dropping sequences, see CREATE 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