The quickest way
to retrieve data from a table is to have a column in the table whose data
uniquely identifies a row. By using this column and a specific value, in the WHERE
condition of a SELECT sentence the Oracle engine will be able to identify
and retrieve the row the fastest.
To achieve this,
a constraint (Primary Key) is attached to a specific column in the table that
ensures that the column is never left empty and that the data values in the
column are unique. Since human beings do data entry, it is quite likely that a
duplicate value could be entered, which violates this constraint and the entire
row is rejected.
If the value
entered into this column is computer generated it will always fulfill the
unique constraint and the row will always be accepted for storage.
Oracle provides
an object called a Sequence that can generate numeric values. The value
generated can have a maximum of 38 digits. A sequence can be defined to:
Generate numbers
in ascending or descending order
Provide
intervals between numbers
Caching of
sequence numbers in memory to speed up their availability
A sequence is an
independent object and can be used with any table that requires its output.
Creating
Sequences
- The minimum
information required for generating numbers using a sequence is:
- The starting
number
- The maximum
number that can be generated by a sequence
- The increment
value for generating the next number.
- This
information is provided to Oracle at the time of sequence creation.
Syntax:
CREATE SEQUENCE
[INCREMENT BY < Integer-Value > START WITH <
Integer-Value > MAXVALUE < Integer-Value > / NOMAX VALUE MINVALUE <
integer-value > / NOMINVALUE CYCLE /
NOCYCLE CACHE < Integer-Value > / NOCACHE ORDER / NOORDER]
Sequence is
always given a name so that it can be referenced later when required.
Keywords And
Parameters
INCREMENT BY: Specifies
the interval between sequence numbers. It can be any positive or negative value
but not zero. If this clause is omitted, the default value is 1.
MINVALUE: Specifies
the sequence minimum value.
NOMINVALUE: Specifies
a minimum value of 1 for an ascending sequence and -(10)^26 for a
descending sequence.
MAXVALUE: Specifies
the maximum value that a sequence can generate.
NOMAXVALUE: Specifies
a maximum of 10^27 for an ascending sequence or -1 for a descending
sequence. This is the default clause.
START WITH: Specifies
the first sequence number to be generated. The default for an ascending
sequence is the sequence minimum value (1) and for a descending sequence, it is
the maximum value (-1).
CYCLE: Specifies
that the sequence continues to generate repeat values after reaching either its
maximum value.
NOCYCLE: Specifies
that a sequence cannot generate more values after reaching the maximum value.
CACHE: Specifies
how many values of a sequence Oracle pre-allocates and keeps in memory for
faster access. The minimum value for this parameter is two.
NOCACHE: Specifies
that values of a sequence are not pre-allocated.
Note : If
the CACHE / NOCACHE clause is omitted ORACLE caches 20 sequence numbers by
default.
ORDER: This
guarantees that sequence numbers are generated in the order of request. This is
only necessary if using Parallel Server in Parallel mode option. In exclusive
mode option, a sequence always generates numbers in order.
NOORDER: This
does not guarantee sequence numbers are generated in order of request. This is
only necessary if you are using Parallel Server in Parallel mode option. If the
ORDER/NOORDER clause is omitted, a sequence takes the NOORDER clause by
default.
Note :
The ORDER, NOORDER Clause has no significance, if Oracle is configured with
Single Server option.
Example 1:
Create a
sequence by the name ADDR_SEQ, which will generate numbers from 1 upto
999 in ascending order with an interval of 1. The sequence must restart
from the number 1 after generating number 999.
Create sequence addr_seq increment by 1 start with 1
MINVALUE 1
MAXVALUE 999 CYCLE;
Referencing A
Sequence
Once a sequence
is created SQL can be used to view the values held in its cache. To simply view
sequence value use a SELECT sentence as described below:
SELECT
.NextVal FROM DUAL;
This will
display the next value held in the cache on the VDU screen. Every time nextval
references a sequence its output is automatically incremented from the old
value to the new value ready for use.
The example
below explains how to access a sequence and use its generated value in the
INSERT statement.
Example 2:
Insert values
for ADDR_TYPE, ADDR1, ADDR2, CITY, STATE and PINCODE in the ADDR_DTLS table.
The ADDR_SEQ sequence must be used to generate ADDR_NO and CODE_NO must
be a value held in the BRANCH_NO column of the BRANCH_MSTR table.
Table Name: ADDR_DTLS
|
Column Name
|
Data Type
|
Size
|
Attributes
|
|
|
ADDR_NO
|
Number
|
6
|
Primary Key
|
|
|
Code_no
|
VarChar2
|
10
|
Foreign Key references BRANCH NO
of the BRANCH_MSTR
table.
|
|
|
ADDR
|
VarChar2
|
50
|
|
|
|
CITY
|
VarChar2
|
25
|
|
|
|
STATE
|
VarChar2
|
25
|
|
|
|
PINCODE
|
VarChar2
|
6
|
|
|
INSERT INTO ADDR_DTLS
(ADDR_NO, CODE_NO, ADDR, CITY, STATE, PINCODE) VALUES(ADDR_SEQ.NextVal, 'B5',
'Vertex Plaza, Western Express Highway, Dahisar (East),', 'Mumbai',
'Maharashtra', '400078');
To reference the
current value of a sequence:
SELECT
.CurrVal FROM DUAL;
This is a method
a numeric value generated by the system, using a sequence can be used to insert
values into a primary key column.
The most
commonly used technique in commercial application development is to concatenate
a sequence-generated value with a user-entered value.
The ADDR NO
stored in the ADDR_DTLS table, can be a concatenation of the month and year
from the system date and the number generated by the sequence ADDR_SEQ. For
example ADDR_NO 01041 is generated with 01 (month in number format), 04 (year
in number format) and l (a sequence generated value).
To help keep the
sequence-generated number from becoming too large, each time either the month
(or year) changes the sequence can be reset.
The sequence can
be reset at the end of each month. If the company generated 50 addresses are
keyed in for the month of January 2004, the ADDR_DTLS will start with 01041
upto 010450. Again when the month changes to February and as the sequence is
reset, the numbering will start with 02041, 02042 and so on.
Using this
simple technique of resetting the sequence at the end of each month and
concatenating the sequence with the system date, unique values can be generated
for the ADDR_NO column and reduce the size of the number generated by the
sequence.
Example 3:
INSERT INTO ADDR_DTLS
(ADDR_NO, CODE_NO, ADDR, CITY, STATE, PINCODE) VALUES ( TO_CHAR(SYSDATE,
'MMYY) || TO_CHAR (ADDR_SEQ.NextVal), 'B5', 'Vertex Plaza, Western Express
Highway, Dahisar (East),', 'Mumbai', 'Maharashtra', '400078');
Altering A
Sequence
A sequence once
created can be altered. This is achieved by using the ALTER SEQUENCE statement.
Syntax:
ALTER SEQUENCE
[INCREMENT BY
MAXVALUE / NOMAXVALUE MINVALUE
/ NOAAINVALUE CYCLE / NOCYCLE CACHE
/ NOCACHE ORDER / NOORDER]
The START value
of the sequence cannot be altered.
Example 4:
Change the Cache
value of the sequence ADDR_SEQ to 30 and interval between two numbers as 2.
ALTER SEQUENCE
ADDRSEQ INCREMENT BY 2 CACHE 30;
Dropping A
Sequence
The DROP
SEQUENCE command is used to remove the sequence from the database.
Syntax:
DROP SEQUENCE
;
Example 5:
Destroy the
sequence ADDR_SEQ.
DROP SEQUENCE ADDR_SEQ;
No comments:
Post a Comment