CREATE SEQUENCE Statement (create_sequence_statement) 

The CREATE SEQUENCE statement (create_sequence_statement) defines a database object that supplies integer values (number generator). In the following description, this object is referred to as a sequence.

Syntax

<create_sequence_statement> ::= CREATE SEQUENCE [<schema_name>.]<sequence_name>
[INCREMENT BY <integer>] [START WITH <integer>]
[MAXVALUE <integer> | NOMAXVALUE] [MINVALUE <integer> | NOMINVALUE]
[CYCLE | NOCYCLE]
[CACHE <unsigned_integer> | NOCACHE]
[ORDER | NOORDER]

Examples

SQL Tutorial, Number Generators for Tables

Explanation

The optional specifications after the sequence_name can be made in any order.

The current user must be a RESOURCE user or database administrator (DBA user) and becomes the owner of the sequence. If no schema_name is specified, the current schema is used.

The sequence is assigned to the schema that has been determined implicitly or specified explicitly. The current user must have the CREATEIN privilege for this schema. The sequence name must be different from the names of all sequences that already exist in the schema.

The integer values generated by the sequence can be used to assign key values.

INCREMENT BY

Defines the difference between the next sequence value and the last value assigned. A negative value for INCREMENT BY generates a descending sequence. If no value is assigned, the value 1 is used.

START WITH

Defines the first sequence value. If no value is specified, the value for MAXVALUE or –1 is used for descending sequences and the value for MINVALUE or 1 for ascending sequences.

MINVALUE | MAXVALUE

·        MINVALUE: Defines the smallest value generated by the sequence. If no value is defined for MINVALUE, when INCREMENT BY has a negative value the smallest integer value that can be represented with 38 digits is used, and when INCREMENT BY has a positive value the value 1 is used.

·        MAXVALUE: Defines the largest value generated by the sequence. If no value is defined for MAXVALUE, when INCREMENT BY has a negative value the value -1 is used, and when INCREMENT BY has a positive value the largest integer value that can be represented with 38 digits is used.

CYCLE | NOCYCLE

If neither CYCLE nor NOCYCLE is specified, NOCYCLE is assumed.

·        CYCLE: MINVALUE is produced for ascending sequences after MAXVALUE has been assigned. MAXVALUE is produced for ascending sequences after MINVALUE has been assigned.

·        NOCYCLE: a request for a sequence value fails if the end of the sequence has already been reached, that is, if MAXVALUE has been assigned for ascending sequences or MINVALUE for descending sequences.

CACHE | NOCACHE

If neither CACHE nor NOCACHE is specified, CACHE 20 is assumed.

·        CACHE: Access to the sequence can be accelerated because the defined number of sequence values is already held in the memory.

·        NOCACHE: There are no predefined sequence values.

ORDER | NOORDER

Specifying ORDER or NOORDER has no effect whatsoever.

Sequence values can be specified using CURRVAL and NEXTVAL (Specification of Values). In this way, you can request or increase the current counter value.