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
- 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 below:
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 above
- 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 prior
to 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 prior to 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;