Sequence pseudo columns

A sequence is a set of incrementing numbers generated by the database according to a specific rule. Typically, these are evenly spaced numerical values. Due to their incrementing nature, sequences are commonly used as primary keys and unique keys.

How to retrieve sequence values

There are two methods to retrieve sequence values:

• CURRVAL: Returns the current value of the sequence.

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

When using a sequence, you must specify the sequence name before CURRVAL and NEXTVAL, and use a period (.) to reference it.

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

Limitations and considerations

• Sequences cannot be used with statements such as HAVING, ORDER BY, and GROUP BY.
• Sequences cannot appear in subqueries, except in the case of INSERT INTO SELECT.
• Sequences cannot appear in WHERE expressions.

Using sequences

The values of CURRVAL and NEXTVAL 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 querying 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 unchanged after multiple executions.*/

When creating a sequence, you must specify its initial value and increment. The first reference to NEXTVAL will return the initial value of the sequence. Subsequent references to NEXTVAL will return a new value by adding the defined increment to the last returned value. Any reference to CURRVAL will always return the current value of the sequence, which is the value returned by the last reference to NEXTVAL.

Before referencing the CURRVAL pseudo column of a sequence in a session, you should first initialize the sequence value for the session by referencing the NEXTVAL pseudo column of the sequence.

When creating a sequence, you can define its initial value and the increment between values. The first reference to NEXTVAL will return the initial value of the sequence. Subsequent references to NEXTVAL will increment the sequence value by the defined amount and return the new value. Any reference to CURRVAL will always return 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