A sequence is a database object that provides a series of integer values.
A sequence has an initial value, an increment value, a min value and a max value defined when it is created (by using the
CREATE SEQUENCEstatement, see Mimer SQL Reference Manual, CREATE SEQUENCE).
A sequence can be defined with or without
CYCLEoption. A sequence without cycle option will generate a series of values that change by the increment value from the initial value to a value that does not exceed the maximum value. A sequence with no cycle option 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.
A sequence is created with an undefined value initially.
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 cycle option 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 cycle option,
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 cycle option 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 cycle option, 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
Voice: +46 18 780 92 00
Fax: +46 18 780 92 40