Sequences

---

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 SEQUENCE statement, see Mimer SQL Reference Manual, CREATE SEQUENCE).

A sequence can be defined with or without CYCLE option. 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 SMALLINT, INTEGER and BIGINT.

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 FOR sequence-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 FOR sequence_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 VALUE must be used before CURRENT VALUE can 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 OF sequence-name will raise an error and the value of the sequence will remain unaltered.

If the sequence has cycle option, NEXT VALUE FOR sequence-name will always succeed.

The value of CURRENT VALUE FOR sequence-name and NEXT VALUE FOR sequence-name can be used where a value-expression would normally be used. The value may also be used after the DEFAULT clause in the CREATE DOMAIN, CREATE TABLE and ALTER TABLE statements.

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.

Examples:

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 info@mimer.se