By using sequences, you can generate unique numbers that can be used for primary key values and other purposes.
A sequence generates a series of values by starting at the initial value and proceeding in increment steps.
Description
A sequence is created with an initial value, an increment and a maximum value. A sequence may either be unique or non-unique. If a unique sequence reaches the maximum defined value, it becomes exhausted, whereas a non-unique sequence will start over from the initial value.
You can get the current value of a sequence, which is maintained on a connection basis. This makes it very easy to get the primary key value for the latest record inserted and use this value when inserting a record as a foreign key column in a referencing table.
Function
You create a sequence using the create sequence statement, which has the following syntax
The default value for INITIAL_VALUE and INCREMENT is 1.
The default for MAX_VALUE is the largest value for an integer, i.e. 2,147,483,647. As an example, consider the following statement:
gives a sequence with the values 1,3,5,7,9,11,2,4,6,8,10,1,3 and so on indefinitely.
On the other hand, the following statement
will give the sequence 1,3,5,7,9,11,2,4,6,8,10 and will stop after this.
The use of a sequence in an SQL-statement is restricted to the creator of the sequence, but the creator may grant or revoke usage on the sequence to other idents in the database.
Techniques
To get values from a sequence, there are two constructs that can be used:
These can be used wherever an expression is possible, as in the following examples:
Mpm Home
Printer friendly
