Purpose
This statement is used to create a sequence. A sequence is a database object that allows you to generate unique integers.
If two users increment the same sequence simultaneously, the sequence numbers obtained by each user may differ by a gap, and one user will never obtain a sequence number generated by the other. Sequence numbers are generated independently of tables, so the same sequence can be used for one or more tables.
After creating a sequence, you can use the CURRVAL pseudo-column in SQL statements to return the current value of the sequence, or use the NEXTVAL pseudo-column to return the next incremental value. For more information, see Sequence Pseudocolumns.
Syntax
CREATE SEQUENCE [ IF NOT EXISTS ] [ schema. ] sequence_name
[ INCREMENT BY int_value ]
[ START WITH int_value ]
[ MAXVALUE int_value | NOMAXVALUE ]
[ MINVALUE int_value | NOMINVALUE ]
[ CYCLE | NOCYCLE ]
[ CACHE int_value | NOCACHE ]
[ ORDER | NOORDER ]
Parameters
field |
Description |
|---|---|
| schema. | Specifies the schema that contains the sequence. If omitted, the database creates the sequence in the schema where the user is currently located. |
| sequence_name | The name of the sequence to be created. |
| IF NOT EXISTS | If the sequence name to be created already exists and no collation is specified. IF NOT EXISTS, an error will be reported. |
| INCREMENT BY int_value | Specifies the auto-increment step for the sequence. int_value must not be 0. If a positive value is specified, the sequence is ascending; if a negative value is specified, the sequence is descending. The default value is 1 if not specified. |
| START WITH int_value | Specifies the starting value of the sequence. int_value must be less than or equal to MAXVALUE and greater than or equal to MINVALUE. If not specified, the default value is the minimum value for ascending order and the maximum value for descending order. |
| MAXVALUE int_value | Specifies the maximum value of the sequence. The value range of int_value is [(-10^{27}+1) \sim (10^{28}-1)]. Notice
|
| NOMAXVALUE | NOMAXVALUE is the default value. If specified as NOMAXVALUE, the maximum value in ascending order is (1028-1), and the maximum value in descending order is -1. |
| MINVALUE int_value | Specifies the minimum value of the sequence. The value range of int_value is [-(1027-1) ~ (1027-1)]. |
| NOMINVALUE | NOMINVALUE is the default value. If specified as NOMINVALUE, the minimum value is 1 for ascending order and -(1027-1) for descending order. |
| CYCLE | Specifies that the sequence values are generated in a cyclic manner. That is, after reaching its maximum or minimum value, the sequence continues to generate values. After reaching the maximum value in ascending order, it generates the minimum value. After reaching the minimum value in descending order, it generates its maximum value. Notice
|
| NOCYCLE | NOCYCLE is the default value, which specifies that the sequence cannot generate more values after reaching its maximum or minimum value. |
| CACHE int_value | Specifies the number of auto-increment values pre-allocated in memory. int_value defaults to 20. The value of CACHE int_value must be greater than 1. If the value of CACHE INT_VALUE is 1, it is equivalent to NOCACHE. |
| NOCACHE | Specifies that sequence values are not pre-allocated. If both CACHE and NOCACHE are omitted, the database caches 20 sequence numbers by default. |
| ORDER | The values of a specified sequence are generated in order. |
| NOORDER | NOORDER is the default value. In this case, the sequence values are not guaranteed to be generated in order. |
Examples
Create a sequence named seq1 in the test schema and specify an increment step of 2. In the following example, the first reference to seq1.nextval returns 1, the second reference returns 3. Each subsequent reference returns a value that is 2 greater than the previous one.
obclient> CREATE SEQUENCE test.seq1 START WITH 1 MINVALUE 1 MAXVALUE 10 INCREMENT BY 2 NOCYCLE NOORDER CACHE 30;
obclient> SELECT seq1.nextval FROM DUAL;
Expected return value:
+--------+
| nextval|
+--------+
| 1 |
+--------+
1 row in set
obclient> SELECT seq1.nextval FROM DUAL;
Expected return value:
+--------+
| nextval|
+--------+
| 3 |
+--------+
1 row in set