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
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
sequence_nameconstruct. 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
sequence_nameconstruct. 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 unique sequence is equal to the last value in the series it defines,
sequence_namewill raise an error and the value of the sequence will remain unaltered.
If the sequence is non-unique,
sequence_namewill 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
sequence_namecan be used where a value-expression would normally be used. The value may also be used after the
DEFAULTclause in the
An ident must hold
USAGEprivilege on the sequence in order to use it.
If a sequence is dropped, with the
CASCADEoption 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