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 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 [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 and specify an increment step of 2. In the following example, the first reference to seq1.nextval returns 1, and 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 result:

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

obclient> SELECT seq1.nextval FROM DUAL;

Expected return result:

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