A
sequence reference is an expression which references a sequence defined
at the application server.
sequence-reference
|--+-| nextval-expression |-+-----------------------------------|
'-| prevval-expression |-'
nextval-expression
|--NEXT VALUE FOR--sequence-name--------------------------------|
prevval-expression
|--PREVIOUS VALUE FOR--sequence-name----------------------------|
- NEXT VALUE FOR sequence-name
- A NEXT VALUE expression generates and returns the next value for
the sequence specified by sequence-name.
- PREVIOUS VALUE FOR sequence-name
- A PREVIOUS VALUE expression returns the most recently generated
value for the specified sequence for a previous statement within the
current application process. This value can be referenced repeatedly
by using PREVIOUS VALUE expressions that specify the name of the sequence.
There may be multiple instances of PREVIOUS VALUE expressions specifying
the same sequence name within a single statement; they all return
the same value. In a partitioned database environment, a PREVIOUS
VALUE expression may not return the most recently generated value.
A PREVIOUS VALUE expression can only be used if a NEXT VALUE expression
specifying the same sequence name has already been referenced in the
current application process, in either the current or a previous transaction
(SQLSTATE 51035).
Notes
- Authorization: If
a sequence-reference is used in a statement, the privileges held by
the authorization ID of the statement must include at least one of
the following privileges:
- The USAGE privilege on the sequence
- DATAACCESS authority
- A new value is generated for a sequence when a NEXT VALUE expression
specifies the name of that sequence. However, if there are multiple
instances of a NEXT VALUE expression specifying the same sequence
name within a query, the counter for the sequence is incremented only
once for each row of the result, and all instances of NEXT VALUE return
the same value for a row of the result.
- The same sequence number can be used as a unique key value in
two separate tables by referencing the sequence number with a NEXT
VALUE expression for the first row (this generates the sequence value),
and a PREVIOUS VALUE expression for the other rows (the instance of
PREVIOUS VALUE refers to the sequence value most recently generated
in the current session), as shown in the following example:
INSERT INTO order(orderno, cutno)
VALUES (NEXT VALUE FOR order_seq, 123456);
INSERT INTO line_item (orderno, partno, quantity)
VALUES (PREVIOUS VALUE FOR order_seq, 987654, 1);
- NEXT VALUE and PREVIOUS VALUE expressions can be specified in
the following places:
- select-statement or SELECT INTO statement (within the select-clause,
provided that the statement does not contain a DISTINCT keyword,
a GROUP BY clause, an ORDER BY clause, a UNION keyword, an INTERSECT
keyword, or EXCEPT keyword)
- INSERT statement (within a VALUES clause)
- INSERT statement (within the select-clause of the fullselect)
- UPDATE statement (within the SET clause (either a searched or
a positioned UPDATE statement), except that NEXT VALUE cannot be
specified in the select-clause of the fullselect of an expression
in the SET clause)
- SET Variable statement (except within the select-clause of the
fullselect of an expression; a NEXT VALUE expression can be specified
in a trigger, but a PREVIOUS VALUE expression cannot)
- VALUES INTO statement (within the select-clause of the fullselect
of an expression)
- CREATE PROCEDURE statement (within the routine-body of an SQL
procedure)
- CREATE TRIGGER statement within the triggered-action (a NEXT VALUE
expression may be specified, but a PREVIOUS VALUE expression cannot)
- NEXT VALUE and PREVIOUS VALUE expressions cannot be specified
(SQLSTATE 428F9) in the following places:
- Join condition of a full outer join
- DEFAULT value for a column in a CREATE or ALTER TABLE statement
- Generated column definition in a CREATE OR ALTER TABLE statement
- Summary table definition in a CREATE TABLE or ALTER TABLE statement
- Condition of a CHECK constraint
- CREATE TRIGGER statement (a NEXT VALUE expression may be specified,
but a PREVIOUS VALUE expression cannot)
- CREATE VIEW statement
- CREATE METHOD statement
- CREATE FUNCTION statement
- An argument list of an XMLQUERY, XMLEXISTS, or XMLTABLE expression
- In addition, a NEXT VALUE expression cannot be specified (SQLSTATE
428F9) in the following places:
- CASE expression
- Parameter list of an aggregate function
- Subquery in a context other than those explicitly allowed, as
described previously
- SELECT statement for which the outer SELECT contains a DISTINCT
operator
- Join condition of a join
- SELECT statement for which the outer SELECT contains a GROUP BY
clause
- SELECT statement for which the outer SELECT is combined with another
SELECT statement using the UNION, INTERSECT, or EXCEPT set operator
- Nested table expression
- Parameter list of a table function
- WHERE clause of the outer-most SELECT statement, or a DELETE or
UPDATE statement
- ORDER BY clause of the outer-most SELECT statement
- select-clause of the fullselect of an expression, in the SET clause
of an UPDATE statement
- IF, WHILE, DO ... UNTIL, or CASE statement in an SQL routine
- When a value is generated for a sequence, that value is consumed,
and the next time that a value is requested, a new value will be generated.
This is true even when the statement containing the NEXT VALUE expression
fails or is rolled back.
If an INSERT statement includes a NEXT
VALUE expression in the VALUES list for the column, and if an error
occurs at some point during the execution of the INSERT (it could
be a problem in generating the next sequence value, or a problem
with the value for another column), then an insertion failure occurs
(SQLSTATE 23505), and the value generated for the sequence is considered
to be consumed. In some cases, reissuing the same INSERT statement
might lead to success.
For example, consider an error that
is the result of the existence of a unique index for the column for
which NEXT VALUE was used and the sequence value generated already
exists in the index. It is possible that the next value generated
for the sequence is a value that does not exist in the index and so
the subsequent INSERT would succeed.
- Scope of PREVIOUS VALUE: The value of PREVIOUS VALUE
persists until the next value is generated for the sequence in the
current session, the sequence is dropped or altered, or the application
session ends. The value is unaffected by COMMIT or ROLLBACK statements.
The value of PREVIOUS VALUE cannot be directly set and is a result
of executing the NEXT VALUE expression for the sequence.
A technique
commonly used, especially for performance, is for an application or
product to manage a set of connections and route transactions to an
arbitrary connection. In these situations, the availability of the
PREVIOUS VALUE for a sequence should be relied on only until the end
of the transaction. Examples of where this type of situation can occur
include applications that use XA protocols, use connection pooling,
use the connection concentrator, and use HADR to achieve failover.
- If in generating a value for a sequence, the maximum value for
the sequence is exceeded (or the minimum value for a descending sequence)
and cycles are not permitted, then an error occurs (SQLSTATE 23522).
In this case, the user could ALTER the sequence to extend the range
of acceptable values, or enable cycles for the sequence, or DROP and
CREATE a new sequence with a different data type that has a larger
range of values.
For example, a sequence may have been defined
with a data type of SMALLINT, and eventually the sequence runs out
of assignable values. DROP and re-create the sequence with the new
definition to redefine the sequence as INTEGER.
- A reference to a NEXT VALUE expression in the select statement
of a cursor refers to a value that is generated for a row of the
result table. A sequence value is generated for a NEXT VALUE expression
for each row that is fetched from the database. If blocking is done
at the client, the values may have been generated at the server before
the processing of the FETCH statement. This can occur when there
is blocking of the rows of the result table. If the client application
does not explicitly FETCH all the rows that the database has materialized,
then the application will not see the results of all the generated
sequence values (for the materialized rows that were not returned).
- A reference to a PREVIOUS VALUE expression in the select statement
of a cursor refers to a value that was generated for the specified
sequence before the opening of the cursor. However, closing the cursor
can affect the values returned by PREVIOUS VALUE for the specified
sequence in subsequent statements, or even for the same statement
in the event that the cursor is reopened. This would be the case when
the select statement of the cursor included a reference to NEXT VALUE
for the same sequence name.
- Syntax alternatives: The following are supported
for compatibility with previous versions of DB2® and with other database products. These alternatives
are non-standard and should not be used.
- NEXTVAL and PREVVAL can be specified in place of NEXT VALUE and
PREVIOUS VALUE
- sequence-name.NEXTVAL can be specified in place
of NEXT VALUE FOR sequence-name
- sequence-name.CURRVAL can be specified in place
of PREVIOUS VALUE FOR sequence-name
Examples
Assume that there is a table called
"order", and that a sequence called "order_seq" is created as follows:
CREATE SEQUENCE order_seq
START WITH 1
INCREMENT BY 1
NO MAXVALUE
NO CYCLE
CACHE 24
Following are some examples of
how to generate an "order_seq" sequence number with a NEXT VALUE expression:
INSERT INTO order(orderno, custno)
VALUES (NEXT VALUE FOR order_seq, 123456);
or
UPDATE order
SET orderno = NEXT VALUE FOR order_seq
WHERE custno = 123456;
or
VALUES NEXT VALUE FOR order_seq INTO :hv_seq;