A sequence is an auto-incrementing numeric sequence number generated by the database according to certain rules. It is typically a set of equally spaced values (of a numeric type). Due to its auto-incrementing nature, it is often used as a primary key and unique key.
Methods for obtaining sequence values
A sequence has the following two methods for obtaining values:
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 and reference them with a period (.).
For example, if the sequence name is SEQ_FOO, you can obtain the current value of the SEQ_FOO sequence through SEQ_FOO.CURRVAL. Similarly, you can obtain the next auto-increment value of the SEQ_FOO sequence through SEQ_FOO.NEXTVAL.
Usage restrictions on sequences
- Cannot be used together with statements such as
HAVING,ORDER BY, andGROUP BY. - Cannot appear in subqueries (except for
INSERT INTO SELECT). - Cannot appear in
WHEREexpressions.
Using sequences
The values of sequence CURRVAL and NEXTVAL can be used in the following positions:
In the select list of a top-level
SELECTstatement.In the
VALUEclause of anINSERTstatement.In the
SETclause of anUPDATEstatement.
The following is an example of querying a sequence independently:
SELECT SEQUENCE_NAME.NEXTVAL FROM DUAL; /* The sequence number increases each time it is executed */
SELECT SEQUENCE_NAME.CURRVAL FROM DUAL; /* The sequence number does not change no matter how many times it is executed */
When creating a sequence, you need to specify its initial value and step size. The first reference to NEXTVAL returns the initial value of the sequence. Subsequent references to NEXTVAL return a new value by adding the defined step size to the last returned value of the sequence. Any reference to CURRVAL always returns 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 reference the NEXTVAL pseudo column of the sequence to initialize the sequence value for this session.
When creating a sequence, you can define its initial value and the increment 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 increment and return the 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 information about creating and deleting 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