Generating sequential values

Generating sequential values is a common database application development problem. The best solution to that problem is to use sequences and sequence expressions in SQL. Each sequence is a uniquely named database object that can be accessed only by sequence expressions.

There are two sequence expressions: the PREVIOUS VALUE expression and the NEXT VALUE expression. The PREVIOUS VALUE expression returns the value most recently generated in the application process for the specified sequence. Any NEXT VALUE expressions occurring in the same statement as the PREVIOUS VALUE expression have no effect on the value generated by the PREVIOUS VALUE expression in that statement. The NEXT VALUE sequence expression increments the value of the sequence and returns the new value of the sequence.

To create a sequence, issue the CREATE SEQUENCE statement. For example, to create a sequence called id_values using the default attributes, issue the following statement:

  CREATE SEQUENCE id_values

To generate the first value in the application session for the sequence, issue a VALUES statement using the NEXT VALUE expression:

VALUES NEXT VALUE FOR id_values

1
-----------
          1

            1 record(s) selected.

To update the value of a column with the next value of the sequence, include the NEXT VALUE expression in the UPDATE statement, as follows:

UPDATE staff
  SET id = NEXT VALUE FOR id_values
  WHERE id = 350

To insert a new row into a table using the next value of the sequence, include the NEXT VALUE expression in the INSERT statement, as follows:

INSERT INTO staff (id, name, dept, job)
  VALUES (NEXT VALUE FOR id_values, 'Kandil', 51, 'Mgr')