Sequences Schema Object:
Ø A
sequence is a schema object that can generate unique sequential values.
Ø The
Sequence values are often used for primary key’s and unique key’s.
Ø To
refer to the current or next value of a sequence in the schema of another user,
the following privileges should be available…
o
Select Object Privilege
o
Select any sequence
Ø For
sequence in other schema the qualifying syntax is
o
SchemaName.SequenceName.CURRVAL
o
SchemaName.SequenceName.NEXTVAL
Ø To
refer to the value of a sequence on a remote database, the sequence should be
qualified with a complete or partial name of the database link.
Ø When
a sequence is created, we can define its Initial value and the increment
between its values.
Ø The
first reference to the NEXTVAL returns the sequence initial value.
Ø Before
the CURRVAL can be used for a sequence in a section, first the sequence should
be incremented with NEXTVAL.
Ø A
Sequence can be accessed by many users concurrently with no waiting, no
locking.
Ø CURRVAL
and NEXTVAL should be qualified with the name of the sequence.
Creating Sequences:
Purpose:
Ø An
Object from which multiple users may generate unique Integers.
Ø Can
be used to generate primary key values automatically.
Syntax:
Create Sequence SequenceName
Increment
by Integer
Start
with Integer
Maxval
Integer/NoMaxValue
Minval
Integer/NoMinValue
Cycle/NoCycle
Cache
Integer/NoCache
Order
/NoOrder;
Note:
Ø Sequence
can be either incremented sequence or decremented sequence.
Increment by Clause:
Ø Specifies
the interval between the sequence numbers.
Ø Value
can be positive or negative, but cannot be 0.
Ø If
the value is positive it is incremented sequence else it is decremented
sequence.
Ø If
omitted defaults to increment by 1.
MINVALUE Clause:
Ø Specifies
the sequence’s minimum value.
NOMINV ALUE Clause:
Ø Specifies
a minimum value of 1 for an ascending sequence or –1 for descending sequence.
MAXVALUE Clause:
Ø Specifies
the maximum value that can be generated.
NOMAXVALUE Clause:
Ø Specifies
a maximum value of 1027 for ascending sequence OR -1 for descending sequence.
CYCLE Clause:
Ø Specifies
the sequence will continue to generate values after reaching either maximum or
minimum value.
NOCYCLE Clause:
Ø Specifies
the sequence cannot generate more values after the targeted limit.
CACHE Clause:
Ø Specifies
the pre-allocation of sequence numbers, the minimum is 2.
NOCACHE Clause:
Ø Specifies
the values of a sequence are not pre-allocated.
ORDER Clause:
Ø Guarantees
the sequence numbers to be generated in the order or request.
NO ORDER Clause:
Ø Does
not guarantee the sequences number with order.
Note:
Ø If
the above parameters are not specified by default
START WITH Will Be 1.
INCREMENT BY Will Be Positive 1.
SEQUENCE IS NOCYCLE.
The CACHE Value Will Be 20.
SEQUENCE IS ORDER Sequence.
Illustrations:
- Create
Table Sample
(
SampID
Number(4)
Constraint
SampID_PK primary key,
SampName
Varchar2(25),
SampDate
Date
);
Creation of Incremental Sequence:
- Create
Sequence SampleSeq
INCREMENT By 1
START WITH 0
MINVALUE 0
MAXVALUE 5
NOCACHE
NOCYCLE;
Activating And Attaching The Sequence To a
Table:
- INSERT INTO
Sample(SampID, SampName, SampDate) Values (SampleSeq.NEXTVAL, ’SAMPLE’,’31-AUG-05’);
Creating A Sequence With Cycle:
- Create
Sequence SampleSeq
INCREMENT
By 1
START
WITH 0
MINVALUE
0
MAXVALUE
5
NOCACHE
CYCLE;
Creation of Decremental Sequence:
- Create
Sequence SampleSeq
INCREMENT
By -1
START
WITH 5
MAXVALUE
5
MINVALUE
0
NOCACHE
NOCYCLE;
Modifying a Sequence:
Ø The
ALTER Command can be used to change the present status of a sequence.
Ø The
ALTER Sequence command can be used to change…
Increment Value
Maximum Value
Minimum Value
Cycle Option
Cache Option
Syntax:
- ALTER
Sequence SequenceName
[INCREMENT
By n]
[{MAXVALUE
n/ NOMAXVALUE}]
[{MINVALUE
n/ NOMINVALUE}]
[{CYCLE
/ NOCYCEL}]
[{CACHE
n / NOCACHE}];
Illustration:
·
Alter
Sequence SampleSeq
MAXVALUE
10
NOCACHE
NOCYCLE;
Guidelines For Altering a Sequence:
Ø The
Alter privilege should be available.
Ø Only
the future sequence numbers are affected by the alter sequence statement.
Ø The
start with option cannot be changed using alter sequence.
Ø To
change the START WITH option, Drop the sequence and then recreate the sequence.
Ø Some
validation performed, i.e A new MAXVALUE cannot be imposed that is less than
the current sequence number.
Viewing the Current value of a Sequence:
·
Select
SampleSeq.CURRVAL From Dual;
Dropping An Existing Sequence:
Ø A
sequence can be dropped at any time.
Ø Once
removed, the sequence can no longer be referenced.
Ex:
·
Drop
Sequence SampleSeq;
Confirming Sequences:
Ø All
Sequences that have been created are documented in the data dictionary.
Ø The
Data dictionary in which the information of sequences are stored is
USER_OBJECTS.
Ø The
settings of the sequence can be confirmed by selecting on USER_SEQUENCES
catalog.
Ø Select
Sequence_Name, Min_Value, Max_Value, Increment_By, Last_Number From
USER_SEQUENCES;