Sequence pseudocolumns

A sequence is a database object that generates incrementing numeric values according to defined rules, typically producing a set of evenly spaced numbers. Because sequences automatically increment, they are often used for primary keys and unique keys.

How to retrieve sequence values

There are two ways to get values from a sequence:

• CURRVAL: Returns the current value of the sequence.

• NEXTVAL: Returns the next incremented value of the sequence.

When you use a sequence, you must prefix CURRVAL and NEXTVAL with the sequence name, separated by a dot.

For example, if your sequence is called SEQ_FOO, you can get its current value with SEQ_FOO.CURRVAL. To get the next value, use SEQ_FOO.NEXTVAL.

Sequence limitations

• Sequences cannot be used with HAVING, ORDER BY, or GROUP BY clauses.
• Sequences cannot appear in subqueries (except in INSERT INTO SELECT statements).
• Sequences cannot be referenced in a WHERE clause.

How to use sequences

You can use the CURRVAL and NEXTVAL pseudocolumns in the following places:

• 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

Here are examples of querying a sequence directly:

SELECT SEQUENCE_NAME.NEXTVAL FROM DUAL;      /* Each time you run this, the sequence value increases */

SELECT SEQUENCE_NAME.CURRVAL FROM DUAL;      /* The sequence value stays the same, no matter how many times you run this */

When you create a sequence, you need to specify its starting value and increment. The first time you reference NEXTVAL, it returns the initial value. Each subsequent reference to NEXTVAL returns the previous value plus the increment you defined. Any time you reference CURRVAL, it returns the current value of the sequence, which is always the value returned by the most recent call to NEXTVAL.

Before you use the CURRVAL pseudocolumn in a session, you should first call NEXTVAL to initialize the sequence value for that session.

When you create a sequence, you can define its starting value and the increment between values. The first time you call NEXTVAL, it returns the starting value. Each time after that, it increases by the amount you specified and returns the new value. Any call to CURRVAL always returns the current value, which is the value from the last NEXTVAL. For details on 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