A sequence is a private database object that can provide a series of integer values. A sequence can be defined as unique or non-unique.
A sequence has an initial value, an increment step value and a maximum value defined when it is created (by using the CREATE SEQUENCE statement).
A unique sequence 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 unique sequence never generates the same value twice.
A non-unique sequence generates a series of values by starting at the initial value and proceeding in increment steps. If all values in a non-unique sequence has been exhausted, the sequence will start over again with the initial value.
A sequence is created with an undefined value initially.
It is possible to generate the next value in the integer series of a sequence by using the NEXT_VALUE OF 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 OF 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 unique sequence 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 is non-unique, NEXT_VALUE OF sequence_name will always succeed. If the current value of the sequence is equal to the last value in the series it defines, the initial value of the sequence will be returned.
The value of CURRENT_VALUE OF sequence_name and NEXT_VALUE OF 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.
A non-unique sequence with initial value 1, increment 3 and maximum 10 will generate the following series of values: 1, 4, 7, 10, 3, 6, 9, 2, 5, 8, 1, 4, 7...
A unique sequence with initial value 1, increment 3 and maximum 10 will generate the following series of values: 1, 4, 7, 10, 3, 6, 9, 2, 5, 8.
Note: It is possible that not every value in the series defined by the sequence will be generated. If a database server crash etc. occurs during the life of a sequence 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