Sequencer objects are objects that are used to get sequence numbers. The sequence objects are used to get sequence numbers in an efficient manner.
The syntax is:
CREATE [DENSE] SEQUENCE sequence_name
Depending on how the sequence is created, there may or may not be holes in the sequence (the sequence can be sparse or dense). Dense sequences guarantee that there are no holes in the sequence numbers. The sequence number allocation is bound to the current transaction. If the transaction rolls back, the sequence number allocations are also rolled back. The drawback of dense sequences is that the sequence is locked out from other transactions until the current transaction ends.
If there is no need for dense sequences, a sparse sequence can be used. A sparse sequence guarantees uniqueness of the returned values, but it is not bound to the current transaction. If a transaction allocates a sparse sequence number and later rolls back, the sequence number is simply lost.
A sequence object can be used, for example, to generate primary key numbers. The advantage of using a sequence object instead of a separate table is that the sequence object is specifically fine-tuned for fast execution and requires less overhead than normal update statements.
Both dense and sparse sequence numbers start from 1.
After creating the sequence with the CREATE SEQUENCE statement, you can access the sequence object values by using the following constructs in SQL statements:
▪sequence_name.CURRVAL which returns the current value of the sequence
▪sequence_name.NEXTVAL which increments the sequence by one and returns the next value.
An example of creating unique identifiers automatically for a table is given below:
INSERT INTO ORDERS (id, ...) VALUES (order_seq.NEXTVAL, ...);
The sequence values can be set dynamically with the SET SEQUENCE statement.
SET SEQUENCE <sequence_name> VALUE <BIGINT_value>
Sequences in stored procedures
Sequences can also be used inside stored procedures.
Stored procedure statement
Usage
EXEC SEQUENCE sequence_name.CURRENT INTO variable
Retrieves a new sequence value
EXEC SEQUENCE sequence_name.NEXT INTO variable
Retrieves the current sequence value
EXEC SEQUENCE sequence_name SET VALUE USING variable
Sets sequence values
Tip: Alternatively, you can use the SET SEQUENCE <name> VALUE <bigint value> statement.
The following example is a stored procedure that retrieves a new sequence number:
"CREATE PROCEDURE get_my_seq RETURNS (val INTEGER) BEGIN EXEC SEQUENCE my_sequence.NEXT INTO (val); END";