Sequence objects are objects that are used to get sequence numbers in an efficient manner, see CREATE SEQUENCE.
Depending on how the sequence is created, there might or might not be gaps in the sequence (the sequence can be sparse or dense). Dense sequences guarantee that there are no gaps 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.
By default, both dense and sparse sequence numbers start from 1.
Sequence numbers are 8-byte values. Sequence values can be stored in BIGINT, BINARY, or INT data types.
▪ BIGINT is recommended.
▪ 8-byte BINARY values can store a complete sequence number, but BINARY values are not always as convenient to work with as integer data types.
▪ Sequence values stored in INT variables lose information because an 8-byte sequence number will not fit in a 4-byte integer.
When an 8-byte number sequence number is stored as a 4-byte integer (in a stored procedure or in an application program), the highest four bytes are omitted. This will cause loss of data when the sequence number is above 2^31 -1 (=2147483647).
The following example demonstrates this behavior:
CREATE SEQUENCE seq1; -- Set the sequence number to 2^31 - 1, -- then return that value and the "next" value (2^31). "CREATE PROCEDURE set_seq1_to_2G RETURNS (x INT, y INT) BEGIN DECLARE int1 INTEGER; int1 := 2147483647; EXEC SEQUENCE seq1 SET VALUE USING int1; EXEC SEQUENCE seq1 CURRENT INTO x; EXEC SEQUENCE seq1 NEXT INTO y; END";
COMMIT WORK; CALL set_seq1_to_2G();
The return values from the call are:
xy 2147483647 -2147483648
The value for x is correct, but the value for y is a negative number instead of the correct positive number.
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.
Select access rights are required to retrieve the current sequence value. Update access rights are required to allocate new sequence values. These access rights are granted and revoked in the same way as table access rights.
An example of creating unique identifiers automatically for a table is shown in the following code:
INSERT INTO ORDERS (id, ...) VALUES (order_seq.NEXTVAL, ...);
The sequence values can be set dynamically with the SET SEQUENCE statement, see SET SEQUENCE.
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
Note Alternatively, you can use the SET SEQUENCEstatement.
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";