Using Sequences

Sequences are the objects that allow you to simulate autonumbered fields with Oracle. DB2 also support sequences.

A sequence is a database object that, when queried, always returns a new value. By default the sequence has a seed of one and an increment value of one. You can create such a sequence with a simple command like this:

CREATE SEQUENCE FriendIDSeq;

You create a sequence with an initial value of 1,000 like this:

CREATE SEQUENCE FriendIDSeq INCREMENT BY 1 START WITH 1000;

You can drop a sequence, like most database objects, using the DROP command:

DROP SEQUENCE FriendIDSeq;

In order to test the FriendIDSeq sequence, let's re-create the Friend table. This time you have a FriendID integer column as the primary key:

CREATE TABLE Friend (
   FriendID INT PRIMARY KEY NOT NULL,
   Name VARCHAR(50),
   PhoneNo VARCHAR(15) DEFAULT 'Unknown Phone');

Once the sequence and the new Friend table are in place, insert some new records into Friend like this:

INSERT INTO Friend (FriendID, Name, PhoneNo)
             VALUES (FriendIDSeq.NextVal, 'Mike', '123');
INSERT INTO Friend (FriendID, Name, PhoneNo)
             VALUES (FriendIDSeq.NextVal, 'John', '456');
INSERT INTO Friend (FriendID, Name, PhoneNo)
             VALUES (FriendIDSeq.NextVal, 'Cath', '789');

Now the Friend table contains something like this:

FriendID  Name                 PhoneNo

--------- -------------------- ---------------
1000      Mike                   123
1001      John                   456
1002      Cath                   789

If you want to see the last sequence value generated, you can call its CurrVal property. This query displays the last value generated by FriendIDSeq:

SELECT FriendIDSeq.CurrVal FROM DUAL;

Note

The DUAL table is a special table with one row and one column named DUMMY of type CHAR(1). This table is useful when you want to get quick information not related to any special table but rather from an external source or function, just like you did for querying the current value of the sequence. Another simple example for using DUAL is getting the current system value using SELECT SYSDATE FROM DUAL or calculating mathematical functions such as SELECT POWER (2,5) FROM DUAL.

Sequences are particularly useful (in conjunction with triggers) for simulating autonumbering in Oracle. You'll look at how to do this in more detail in Chapter 13, "Triggers," where you look at triggers in depth, but let's look at a simple example here of how to do this in the different database systems.