 |
|
Oracle Tips by Burleson |
Sequences
A sequence is a database object that provides a
number when requested. Like a view, a sequence is just the definition
and the current number. When a sequence is asks for a number, it
looks at the current number and provides the next number as per the
sequence definition.
SQL> create
sequence pubs1;
Sequence created.
SQL> select
pubs1.nextval from dual;
NEXTVAL
----------
1
SQL> select
pubs1.nextval from dual;
NEXTVAL
----------
2
In the example above, we created a sequence called
pubs1. We use the NEXTVAL function to retrieve the next
value. You can get the current value by calling the CURRVAL
function. You must call the NEXTVAL function before calling the
CURRVAL function, or you will get an error. There is no current value
to the sequence until the next value has been called at least once.
When you create the sequence, you have a lot of
flexibility as to how the sequence generates the next number.
SQL> create
sequence pubs2
2 start with 8
3 increment by 2
4 maxvalue 10000
5 cycle
6 cache 5;
Sequence
created.
SQL> select
pubs2.nextval from dual;
NEXTVAL
----------
8
The sequence pubs2 will start at number
eight and increment by twos to 10000 and then start over. Let’s cover
each of their parameters:
§
START WITH
– defines the first number of the
sequence. Default is one.
§
INCREMENT
– defines how many to add to get
the next values. Default is one.
§
MINVALUE
– defines the lowest value of the
sequence when the sequence is created to count down (increment by a
minus number).
§
MAXVALUE
– defines the largest value of the
sequence. Default is 10E23.
§
CYCLE/NOCYCLE – tells the sequence to start over once it reaches the
maxvalue or minvalue. The default is NOCYCLE.
§
CACHE/NOCACHE – tells the database how many numbers to cache in
memory. The default is 20. So, if the sequence is starting at one,
the database will cache 1..20 and set the sequence at 21. The
database will answer the NEXTVAL request from the numbers in memory.
Once those numbers are used, the database will load 20 more numbers in
memory. If the database is shutdown, all sequence numbers in memory
will be lost. When the database restarts, it will look at the number
for the sequence and load the next 20 into memory, setting the
sequence forward by 20. This improves response but will lose some
numbers. Setting the NOCACHE will cause the database not to
cache any numbers in memory.
Sequences are relatively simple objects designed
to provide a series of numbers. No call to NEXTVAL will result in the
same number unless the sequence cycles. One of the main uses of a
sequence is to provide a primary key for a
table where the rows do not naturally define a unique number. In our
AUTHOR table, the primary key is author_key, and it is formatted as
A101, A102, etc. I can use a sequence to generate this key.
SQL> select
pubs2.nextval from dual;
NEXTVAL
----------
12
SQL> insert
into author values
2 (
3 'A'||to_char (pubs2.nextval + 100),
4 'thumbmasher',
5 'philbert',
6 '457-934-2642',
7 '1320 leaning tree ln',
8 'pie town',
9 'IL',
10 '57307',
11 '7896');
1 row created.
SQL> select
author_key, author_last_name from author;
AUTHOR_KEY
AUTHOR_LAST_NAME
----------- ----------------------------------------
A101 jones
A102 hester
A103 weaton
A104 jeckle
A105 withers
A106 petty
A107 clark
A108 mee
A109 shagger
A110 smith
A114 thumbmasher
11 rows
selected.
Remember that we created pubs2 to increment
by two. Once you learn PL/SQL
, you will be able to create a
trigger that will automatically create the key on INSERT.
From the simple sequence, we move to one of the
most complicated object in the database, the index.
The above book excerpt is from:
Easy Oracle
SQL
Get Started
Fast writing SQL Reports with SQL*Plus
ISBN 0-9727513-7-8
Col. John Garmany
http://www.rampant-books.com/book_2005_1_easy_sql.htm |
