Filter
Top Products
6.13 Manipulating Data Using Sequence
CURRVAL and NEXTVAL can be used to fetch the created sequence number. CURRVAL fetches the current
sequence number in the application program. NEXTVAL fetches the next value after the last sequence number
fetched in the RDB system.
CURRVAL and NEXTVAL can be specified in the following parts of SQL statements in the application program or
stored procedure:
· Select column list of a single row SELECT statement
· Select column list for a query specification (query expression for cursor specification only)
· SET clause of the UPDATE statement
· Select column list for a query specification specified in the INSERT statement
· VALUES clause of the INSERT statement
· SET statement of a stored procedure
· Triggered SQL statement
CURRVAL and NEXTVAL cannot be specified in the following parts of SQL statements:
· Search condition of a WHERE clause
· Select column list for a subquery
· DEFAULT clause of ALTER TABLE ADD COLUMN
For the SELECT statement, sequence numbers are assigned to the rows of a search result.
For the UPDATE statement, sequence numbers are assigned to update rows. For the INSERT statement, sequence
numbers are assigned to the insertion rows of the insertion destination. In either case, if there are several search
results, sequence numbers are assigned to each row of the results.
To fetch the first sequence in a session, use NEXTVAL.
If several NEXTVALs of the same sequence are specified in one SQL statement, the same sequence number is
returned for all the NEXTVALs. If both the NEXTVAL and CURRVAL of the same sequence are specified in one SQL
statement, the same sequence number is returned for both without reference to the order in which they appear in the
SQL statement.
Example 1:
126