Number Generators for Tables

You can use the CREATE SEQUENCE statement to generate a sequence, and thus, in turn, an implicit number generator (automatically-generated sequence of numbers). This is commonly used for generating a primary key, for example.

Each time you access a sequence, you can determine the next available value of the number generator and use it. You can get the values of the number generator from the pseudo columns NEXTVAL and CURRVAL. These values are always integers.

NEXTVAL shows the next available value generated by the number generator
CURRVAL shows the current value generated by the number generator within the database session

Using INSERT and UPDATE statements, the current and next available sequence values can be entered in any column of a table if the data type of the column allows it. It is not possible, however, to define the next available or current value as the DEFAULT value of a column. Both values must always be specified explicitly.

Prerequisites

You can use the demo data for the SQL tutorial. Start the Database Studio as database administrator MONA with the password RED and log on to demo database DEMODB: Logging On to a Database.

Activities

You can use Database Studio to enter and execute SQL statements. More information: Working with SQL Statements: Overview

Creating a Sequence

Use the CREATE SEQUENCE statement to create the sequence sequ. Enter the sequence name and the first sequence value (START WITH <integer>, default value 1). Specify the increment by which the next value in the sequence is to be generated (INCREMENT BY <integer>, default value 1).

CREATE SEQUENCE hotel.sequ

START WITH 1

INCREMENT BY 1

See also: SQL Reference Manual, CREATE SEQUENCE Statement (create_sequence_statement)

Using a Sequence

For the following example, create the tables pers1 and pers2.

CREATE TABLE hotel.pers1

(pno FIXED(6),

firstname CHAR (20),

name CHAR(20))

//

CREATE TABLE hotel.pers2

(pno FIXED(6),

firstname CHAR (20),

name CHAR(20))

One Number Generator for Different Tables

You can use the number generator generated with the sequ sequence in your SQL statements. To do so, enter the keyword NEXTVAL after the sequence name. If you want to use the number generator in different tables, specify this in the desired tables with <sequence_name>.NEXTVAL.

INSERT INTO hotel.pers1 VALUES (hotel.sequ.NEXTVAL,'Mary','Jackson')

//

INSERT INTO hotel.pers2 VALUES (hotel.sequ.NEXTVAL,'Billy','Jackson')

//

INSERT INTO hotel.pers1 VALUES (hotel.sequ.NEXTVAL,'Lucy','Baker')

//

INSERT INTO hotel.pers2 VALUES (hotel.sequ.NEXTVAL,'Henry','Baker')

//

INSERT INTO hotel.pers1 VALUES (hotel.sequ.NEXTVAL,'Anna','Miller')

//

INSERT INTO hotel.pers2 VALUES (hotel.sequ.NEXTVAL,'Mike','Miller')

//

INSERT INTO hotel.pers1 VALUES (hotel.sequ.NEXTVAL,'Peggy','Miller')

//

SELECT * FROM hotel.pers1

Result

`PNO`	`FIRSTNAME`	`NAME`
`1`	`Mary`	`Jackson`
`3`	`Lucy`	`Baker`
`5`	`Anna`	`Miller`
`7`	`Peggy`	`Miller`

SELECT * FROM hotel.pers2

Result

`PNO`	`FIRSTNAME`	`NAME`
`2`	`Billy`	`Jackson`
`4`	`Henry`	`Baker`
`6`	`Mike`	`Miller`

Displaying the Current Value of the Number Generator

You can use the DUAL table, which is automatically generated by the database system, to display the current value of the number generator. The DUAL table can be used by all database users. As the DUAL table has only one row, you can use it to display the value of the number generator using a SELECT statement.

SELECT hotel.sequ.CURRVAL FROM dual

Result

	`EXPRESSION1`
`1`	`7`

If <sequence_name>.NEXTVAL has not been used in the current database session, <sequence_name>.CURRVAL cannot be utilized.

See also: SQL Reference Manual, Specification of Values (extended_value_spec)

Using a Number Generator Value More Than Once

One of the ways to utilize a value from a number generator more than once is as follows: Enter the current value of the number generator <sequence_name>.CURRVAL in your SQL statement.

INSERT INTO hotel.pers1 VALUES (hotel.sequ.NEXTVAL,'Mary','Ford')

//

INSERT INTO hotel.pers2 VALUES (hotel.sequ.CURRVAL,'Mary','Ford')

//

SELECT * FROM hotel.pers1

Result

`PNO`	`FIRSTNAME`	`NAME`
`1`	`Mary`	`Jackson`
`3`	`Lucy`	`Baker`
`5`	`Anna`	`Miller`
`7`	`Peggy`	`Miller`
`8`	`Mary`	`Ford`

SELECT * FROM hotel.pers2

Result

`PNO`	`FIRSTNAME`	`NAME`
`2`	`Billy`	`Jackson`
`4`	`Henry`	`Baker`
`6`	`Mike`	`Miller`
`8`	`Mary`	`Ford`

Deleting the Number Generator

To delete a number generator, use the DROP SEQUENCE statement.

DROP SEQUENCE hotel.sequ

See also: SQL Reference Manual, DROP SEQUENCE Statement (drop_sequence_statement)

More Information

More examples for Data Definition