This topic describes the functions that PolarDB for Oracle provides to manage sequence objects.

A sequence object (also known as a sequence generator or sequence) is a specific single-row table created by the CREATE SEQUENCE command. A sequence object is used to generate unique identifiers for rows of a table. The following sequence functions provide simple and multiuser-safe methods for obtaining successive sequence values from sequence objects.

Syntaxes

sequence.NEXTVAL 
sequence.CURRVAL

Parameters

  • sequence: the identifier assigned to the sequence in the CREATE SEQUENCE command. The following section describes how to use the preceding functions.
  • NEXTVAL: This function advances the sequence object to its next value and returns the value. This operation cannot be reversed after it is complete. If multiple sessions concurrently run the NEXTVAL function, each session will safely receive a distinct sequence value.
  • CURRVAL: This function returns the value that is most recently obtained by the NEXTVAL function for the specified sequence in the current session. (If NEXTVAL has never been called for this sequence in this session, an error is reported.) Note that this function returns a session-local value. It provides a predictable answer to whether other sessions have run NEXTVAL since the current session ran NEXTVAL.

If a sequence object has been created by using default parameters, calls to the NEXTVAL function on this object will return successive values starting with 1. You can use specific parameters in the CREATE SEQUENCE command to obtain other behavior.

Note To avoid blocking concurrent transactions that obtain numbers from the same sequence, you cannot roll back a NEXTVAL operation. Once a value has been retrieved, it is considered used. The value is still considered used if the transaction that did the NEXTVAL later aborts. This means that aborted transactions may leave unused "gaps" in the sequence of assigned values.