A sequence is a series of auto-increment numeric identifiers generated by a database according to specific rules. Typically, it consists of a set of equidistant values of a numeric data type. Due to its auto-increment property, it is commonly used as the primary key or unique key.
Methods for obtaining sequence values
You can obtain the sequence value in the following two ways:
CURRVAL: Returns the current value of the sequence.NEXTVAL: Returns the next auto-increment value of the sequence.
When using a sequence, you must prefix CURRVAL and NEXTVAL with the sequence name, separated by a period (.).
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 auto-increment value of the SEQ_FOO sequence.
Limitations on sequences
- It cannot be used together with statements such as
HAVING,ORDER BY, andGROUP BY. - It cannot be used in subqueries (except for
INSERT INTO SELECT). - It cannot be used in the
WHEREexpression.
Usage of sequences
The values of the CURRVAL and NEXTVAL sequences can be used in the following contexts:
In the select-list of the top-level
SELECTstatement.The
VALUEclause in anINSERTstatement.In the
SETclause of anUPDATEstatement.
The following example shows how to query sequences separately:
SELECT SEQUENCE_NAME.NEXTVAL FROM DUAL; /*The sequence number increments with each execution.*/
SELECT SEQUENCE_NAME.CURRVAL FROM DUAL; /*The sequence number does not change after multiple executions.*/
When creating a sequence, you must specify its initial value and step. The first reference to NEXTVAL returns the sequence's initial value. Subsequent references to NEXTVAL return a new value obtained by adding the step defined in the sequence to the previous returned value. Any reference to CURRVAL always returns the current sequence value, which is the value returned by the most recent NEXTVAL reference.
Before referencing the CURRVAL pseudo-column of a sequence in a session, you must first use the NEXTVAL pseudo-column of the sequence to initialize the sequence value for the current session.
When creating a sequence, you can define its initial value and the increment between values. The first reference to NEXTVAL returns the sequence's initial value. Subsequent references to NEXTVAL increment the sequence value by the defined increment and return the new value. Any reference to CURRVAL always returns the sequence's current value, which is the value returned by the last NEXTVAL reference. For 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