CREATE SEQUENCE

Purpose

This statement is used to create a sequence. A sequence is a database object that generates unique integers.

If two users increment the same sequence at the same time, each user may receive a sequence number with a gap, and one user will never receive the sequence number generated by the other user. Sequence numbers are generated independently of tables, so the same sequence can be used for one or more tables.

After a sequence is created, you can use the CURRVAL pseudocolumn in an SQL statement to return the current value of the sequence or use the NEXTVAL pseudocolumn to return the incremented new value. For more information, see Sequence pseudocolumns.

Syntax

CREATE [WITH HINT] SEQUENCE [ schema. ] sequence_name
[ INCREMENT BY simple_num ]
[ START WITH simple_num ]
[ MAXVALUE simple_num | NOMAXVALUE ]
[ MINVALUE simple_num | NOMINVALUE ]
[ CYCLE | NOCYCLE ]
[ CACHE simple_num | NOCACHE ]
[ ORDER | NOORDER ]
[ RESTART ]

Parameters

Examples

Create a sequence named seq1 in the test schema with an increment step of 2. In the following example, the first reference to seq1.nextval returns 1, the second reference returns 3, and 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 result:

+--------+
| nextval|
+--------+
| 1      |
+--------+
1 row in set

obclient> SELECT seq1.nextval FROM DUAL;

Expected return result:

+--------+
| nextval|
+--------+
| 3      |
+--------+
1 row in set