Sequence pseudocolumns

A sequence is a group of auto-increment serial numbers generated by the database based on certain rules, typically a group of numeric values (numbers) with an equal interval. A sequence is often used as the primary key or unique key.

Reference a sequence

You can use the following sequence pseudocolumns to reference sequence values in SQL statements:

• CURRVAL: returns the current value of the sequence.

• NEXTVAL: returns the next incremental value.

To use a sequence, 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.

Use a sequence

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

• The select list of a SELECT statement.

• The VALUES clause of an INSERT statement.

• The SET clause of an UPDATE statement.

Here are examples of using NEXTVAL and CURRVAL to obtain a sequence value:

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

SELECT SEQUENCE_NAME.CURRVAL FROM DUAL;     /*The sequence value does not change no matter how many times the statement is executed.*/

When you create a sequence, you need to define its start value and increment. 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 and drop a sequence, 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