CREATE SEQUENCE

Purpose

You can use this statement to create a sequence. A sequence is a database object. You can generate a unique integer from a sequence.

If two users increment the same sequence concurrently, the sequence numbers obtained by the two users have an interval. One user can never obtain the sequence number generated by the other user. Sequence numbers are generated independently of the table. Therefore, you can use the same sequence for multiple tables.

After creating a sequence, you can use the CURRVAL pseudocolumn to return the current sequence value or use the NEXTVAL pseudocolumn to return the new increment value in the SQL statement. For more information, see Sequence pseudocolumns.

Syntax

CREATE SEQUENCE [ IF NOT EXISTS ] [ 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

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