Purpose
You can use this statement to create a sequence. Sequences are database objects. You can generate a unique integer from a sequence.
If two users increment the same sequence concurrently, the sequence numbers obtained by each user may be inconsecutive. One user can never obtain the sequence numbers generated by the other user. Sequence numbers are generated independent of the table. Therefore, you can use the same sequence for one or more tables.
After you create a sequence, you can use the CURRVAL pseudocolumn in the SQL statement to return the current sequence value or use the NEXTVAL pseudocolumn to return the new sequence value. For more information, see Sequence pseudocolumns.
Syntax
CREATE SEQUENCE [ schema. ] sequence_name
{ START WITH int_value
|[ INCREMENT BY int_value ]
|[ MINVALUE int_value | NOMINVALUE ]
|[ MAXVALUE int_value | NOMAXVALUE ]
|[ CACHE int_value | NOCACHE ]
|[ ORDER | NOORDER ]
|[ CYCLE | NOCYCLE ]
}
;
Parameters
| Parameter | Description |
|---|---|
| schema. | The schema where the sequence is located. If no schema is specified, the database creates a sequence in the current schema. |
| sequence_name | The name (optionally schema-qualified) of the sequence to be created. |
| MINVALUE int_value | The minimum value of the sequence. Value range of int_value: [-9223372036854775808, 9223372036854775807] |
| NOMINVALUE | NOMINVALUE is the default value. If NOMINVALUE is specified, the minimum value is 1 for an ascending sequence and is -9223372036854775808 for a descending sequence. |
| MAXVALUE int_value | The maximum value of the sequence. Value range: [-9223372036854775808, 9223372036854775807] Notice
|
| NOMAXVALUE | NOMAXVALUE is the default value. If NOMAXVALUE is specified, the maximum value is 9223372036854775807 for an ascending sequence and is -1 for a descending sequence. |
| START WITH int_value | The start value of the sequence. int_value must be smaller than or equal to MAXVALUE, and greater than or equal to MINVALUE. If no start value is specified, the start value is the minimum value for an ascending sequence and the maximum value for a descending sequence. |
| INCREMENT BY int_value | The increment of the sequence. int_value cannot be 0. If the value is positive, the sequence ascends. If the value is negative, the sequence descends. If this parameter is not specified, the default value is 1. |
| CACHE int_value | The number of auto-increment values pre-allocated in the memory. Default value: 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 the sequence values are not pre-allocated. If both CACHE and NOCACHE are omitted, the database caches 20 sequence numbers by default. |
| ORDER | Specifies that the sequence values are generated in order. |
| NOORDER | NOORDER is the default value, which means it is not assured that the sequence values are generated in order. |
| CYCLE | Specifies that the sequence values are generated cyclically. That is, when the sequence reaches its maximum or minimum value, it continues to generate values. When the sequence ascends and reaches its maximum value, it generates the minimum value. When the sequence descends and reaches its minimum value, it generates the maximum value. Notice
|
| NOCYCLE | NOCYCLE is the default value, which means that the sequence does not generate more values when it reaches its maximum or minimum value. |
Examples
Create a sequence named seq1 in the test schema and set the increment to 2. In the following example, 1 is returned for the first reference to seq1.nextval, and 3 is returned for the second reference. The return value of each subsequent reference is greater than that of the previous reference by 2.
obclient> CREATE SEQUENCE test.seq1 START WITH 1 MINVALUE 1 MAXVALUE 10 INCREMENT BY 2 NOCYCLE NOORDER CACHE 30;
Query OK, 0 rows affected
obclient> SELECT seq1.nextval FROM DUAL;
+--------+
| nextval|
+--------+
| 1 |
+--------+
1 row in set
obclient> SELECT seq1.nextval FROM DUAL;
+--------+
| nextval|
+--------+
| 3 |
+--------+
1 row in set