Example of sequences, Database Management System

Example: Create a sequence named SEQSS that starts at 105, has a step of 1 and can take maximum value as 2000.

CREATE SEQUENCE SEQSS START WITH 105

INCREMENT BY 1

MAX VALUE 2000;

How the sequence so formed is used? The following sequence of commands try to illustrate the use of the sequence SEQSS.

Suppose a table person exists as: SELECT * FROM PERSON;

Output: CODE NAME ADDRESS

------ ------- -------- ---------------

104 RAMESH MUMBAI

Now, if we give the command:

INSERT INTO PERSON

VALUES (SEQSS.NEXTVAL, &NAME, &ADDRESS)

On implementation of statement above do the following input:

Enter value for name: 'Rakhi'

Enter value for address: 'New Delhi'

Now, here is the following command to see the output:

SELECT * FROM PERSON;

CODE NAME ADDRESS

--------- --------------- -----------------

104 RAMESH MUMBAI

105 Rakhi NEW DELHI

The explanations of sequences such as minimum value, maximum value, increment or step are stored in the data dictionary. For instance, in oracle it is stored in the table user_sequences. You can see the explanation of sequences by giving the SELECT command.