Sequences

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.

o   SchemaName.SequenceName.CURRVAL@DBLINK

o   SchemaName.SequenceName.NEXTVAL@DBLINK

Ø  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;