Creating Sequences
13
Introduction to Oracle: SQL and PL/SQL Using Procedure Builder13Ć2
Schedule: Timing Topic
20 minutes Lecture
25 minutes Practice
45 minutes Total
Class Management Note:
Files required for this lesson are:
Demonstration: l13dd.sql
Practice: None
Creating Sequences 13Ć3
Objectives
Many applications require the use of unique numbers as primary key values.
You can either build code into the application to handle this requirement or use
a sequence to generate unique numbers. This lesson covers creating and using
sequences that create unique numbers.
At the end of this lesson, you should be able to
D
Explain the use of sequences.
D
Create a sequence.
D
Use a sequence.
D
Modify a sequence definition.
D
Remove a sequence.
Introduction to Oracle: SQL and PL/SQL Using Procedure Builder13Ć4
Creating Sequences 13Ć5
Overview

}]
[{MINVALUE n | NOMINVALUE
}]
[{CYCLE | NOCYCLE
}]
[{CACHE n | NOCACHE}]
where: sequence is the name of the sequence generator.
INCREMENT BY n specifies the interval between sequence numbers
where n is an integer. If this clause is omitted,
the sequence will increment by 1.
START WITH n specifies the first sequence number to be
generated. If this clause is omitted, the sequence
will start with 1.
MAXVALUE n specifies the maximum value the sequence can
generate.
NOMAXVALUE specifies a maximum value of 10
27
. This is the
default option.
MINVALUE n specifies the minimum sequence value.
NOMINVALUE specifies a minimum value of 1.
CYCLE | NOCYCLE specifies that the sequence continues to generate
values after reaching either its maximum or
minimum value or does not generate additional
values. NOCYCLE is the default option.
CACHE n | NOCACHE specifies how many values the Oracle7 Server
will preallocate and keep in memory. By
default, the Server will cache 20 values.
For more information, see
Oracle7 Server SQL Reference, Release 7.3, “CREATE SEQUENCE.”

If you use the CACHE OPTION, LAST_NUMBER displays the next value
after all the numbers in the cache are used.