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 require the demo data for the SQL Tutorial.

Start the query tool SQL Studio as database administrator MONA with password RED and log on to the demo database instance DEMODB.

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

 

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

 

See also:

Number Generator for a Single Table Column

 

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 examples for Data Definition