Sequence Manipulation Functions v11
This section describes Advanced Server’s functions for operating on sequence objects. Sequence objects (also called sequence generators or just sequences) are special single-row tables created with the CREATE SEQUENCE
command. A sequence object is usually used to generate unique identifiers for rows of a table. The sequence functions, listed below, provide simple, multiuser-safe methods for obtaining successive sequence values from sequence objects.
sequence
is the identifier assigned to the sequence in the CREATE SEQUENCE
command. The following describes the usage of these functions.
NEXTVAL
Advance the sequence object to its next value and return that value. This is done atomically: even if multiple sessions execute NEXTVAL
concurrently, each will safely receive a distinct sequence value.
CURRVAL
Return the value most recently obtained by NEXTVAL
for this sequence in the current session. (An error is reported if NEXTVAL
has never been called for this sequence in this session.) Notice that because this is returning a session-local value, it gives a predictable answer whether or not other sessions have executed NEXTVAL
since the current session did.
If a sequence object has been created with default parameters, NEXTVAL
calls on it will return successive values beginning with 1. Other behaviors can be obtained by using special parameters in the CREATE SEQUENCE
command.
Important: To avoid blocking of concurrent transactions that obtain numbers from the same sequence, a NEXTVAL
operation is never rolled back; that is, once a value has been fetched it is considered used, even if the transaction that did the NEXTVAL
later aborts. This means that aborted transactions may leave unused "holes" in the sequence of assigned values.