Sequence pseudo column

A sequence is a set of incrementing numbers generated by a database according to a specific rule. Usually, a sequence consists of values with equal intervals (of the numeric type). Because of its incrementing feature, a sequence is often used as the primary key or unique key.

How to get the value of a sequence

The value of a sequence can be obtained 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 and NEXTVAL, and separate the sequence name from the pseudo column by using a period (.) to reference the pseudo column.

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. Similarly, you can use SEQ_FOO.NEXTVAL to obtain the next value of the SEQ_FOO sequence.

Limitations on the use of sequences

• You cannot use a sequence with the HAVING, ORDER BY, or GROUP BY clause.
• You cannot use a sequence in a subquery, except for the INSERT INTO SELECT statement.
• You cannot use a sequence in a WHERE expression.

Use sequences

The values of the CURRVAL and NEXTVAL pseudo columns of a sequence can be used in the following scenarios:

• 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 are some examples of queries that use sequences:

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 unchanged after it is executed multiple times. */

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 will return a new value, which is the previous value of the sequence plus the defined increment. Any reference to CURRVAL will return 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 define 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 will return a new value, which is the previous value of the sequence plus the defined increment. Any reference to CURRVAL will return the current value of the sequence, which is the value returned by the last reference to NEXTVAL. For more information about how to create and drop a sequence, see CREATE SEQUENCE and DROP SEQUENCE.

Examples

CREATE SEQUENCE s1 START WITH 95 INCREMENT BY 1 NOORDER CACHE 10000;
Query OK, 0 rows affected (0.001 sec)

CREATE TABLE tbl1 (i INT,j INT);
Query OK, 0 rows affected (0.001 sec)

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

SELECT * FROM tbl1;
+---+------+
| I | J    |
+---+------+
| 1 |   70 |
| 2 |   71 |
| 3 |    3 |
| 4 |    4 |
+---+------+
4 rows in set (0.003 sec)

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 (0.003 sec)

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 (0.003 sec)

UPDATE tbl1 SET i = s1.nextval;
Query OK, 4 rows affected (0.003 sec)
Rows matched: 4  Changed: 4  Warnings: 0

SELECT * FROM tbl1;
+-----+------+
| I   | J    |
+-----+------+
| 103 |   70 |
| 104 |   71 |
| 105 |    3 |
| 106 |    4 |
+-----+------+
4 rows in set (0.003 sec)

SELECT s1.currval FROM DUAL;
+---------+
| currval |
+---------+
| 106     |
+---------+
1 row in set (0.001 sec)