A sequence is a database object that provides a series of integer values.
A sequence has an initial value, an increment value, a minimum value and a maximum value defined when it is created, either implicitly or explicitly (by using the
CREATE SEQUENCEstatement, see Mimer SQL Reference Manual, CREATE SEQUENCE).
A sequence can be defined with
NO CYCLEoption. A sequence with
CYCLEoption may re-use values when the maximum value has been reached. A sequence with
NO CYCLEoption never generates the same value twice.
A sequence definition may contain a data type which determines the limits for which values that can be generated by using the sequence. The allowed data types are
A sequence generates a series of values by starting at the initial value and proceeding in increment steps. If all values in a sequence with cycle option has been exhausted, the sequence will start over again with the min value if the increment is positive, and with the max value if the increment is negative.
It is possible to generate the next value in the value series of a sequence by using the
NEXT VALUE FORsequence-name construct. This is used for the first time after the sequence has been created to establish the initial value defined for the sequence. Subsequent uses will add the increment step value to the value of the sequence and the result will be established as the current value of the sequence.
It is possible to get the value of a sequence by using the
CURRENT VALUE FORsequence_name construct. This construct cannot be used until the initial value has been established for the sequence (i.e. using it immediately after the sequence has been created will raise an error). For each new database connection,
NEXT VALUEmust be used before
CURRENT VALUEcan be used.
When the current value of a sequence with
NO CYCLEoption is equal to the last value in the series it defines,
NEXT VALUE OFsequence-name will raise an error and the value of the sequence will remain unaltered.
If the sequence has
NEXT VALUE FORsequence-name will always succeed.
The value of
CURRENT VALUE FORsequence-name and
NEXT VALUE FORsequence-name can be used where a value-expression would normally be used. The value may also be used after the DEFAULT clause in the
An ident must hold USAGE privilege on the sequence in order to use it.
If a sequence is dropped, with the CASCADE option in effect, all object referencing the sequence will also be dropped.
A sequence with
CYCLEoption with start value 1, increment 3 and maximum 10 will generate the following series of values: 1, 4, 7, 10, 1, 4, 7, 10, 1, 4...
A sequence with
NO CYCLEoption, start value 1, increment 3, minvalue 1 and maxvalue 10 will generate the following series of values: 1, 4, 7, 10.
Note: It is possible that not every value in the series defined by the sequence will be generated. If a server failure occurs it is possible that some of the values in the series might be skipped.
Mimer Information Technology AB
Phone: +46 18 780 92 00