SQL Sequences | Nextval | Currval | Modifying and Dropping a Sequence | Basics of Oracle SQL | Oracle SQL | SQL

 Sequences

A sequence is a user created database object that can be shared by multiple users to generate unique integers.

A typical usage for sequences is to create a primary key value, which must be unique for each row.

The sequence is generated and incremented (or decremented) by an internal Oracle routine.

Sequence numbers are stored and generated independently of tables. Therefore, the same sequence can be used for multiple tables.

A sequence Speeds up the efficiency of accessing sequence values when cached in memory

A sequence Replaces application code.

 

The create sequence statement

 

Syntax:

                  CREATE SEQUENCE sequencename

                                [INCREMENT BY n]

                                [START WITH n]

                                [{MAXVALUE n | NOMAXVALUE}]

                                [{MINVALUE n | NOMINVALUE}]

                                [{CYCLE | NOCYCLE}]

                                [{CACHE n | NOCACHE}];

In the syntax:

     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 increments 

                                   by 1.)

     START WITH n    specifies the first sequence number to be generated (If this

                                   clause is omitted, the sequence starts with 1.)

Creating a Sequence : 

CREATE SEQUENCE XXDEPT_S

                                   INCREMENT BY 10

                                   START WITH 120

                                   MAXVALUE 9999

                                   NOCACHE

                                   NOCYCLE;

 

Verify your sequence values in the USER_SEQUENCES data dictionary table.

NEXTVAL:

The NEXTVAL pseudo column is used to extract successive sequence numbers from a specified sequence.

 You must qualify NEXTVAL with the sequence name. When you reference sequence.NEXTVAL, a new sequence number is generated and the current sequence number is placed in CURRVAL.

CURRVAL:

The CURRVAL pseudocolumn is used to refer to a sequence number that the current user has just generated.

You must qualify CURRVAL with the sequence name. When sequence.CURRVAL is referenced, the last value returned to that user’s process is displayed.

 

Modifying a Sequence :

Change the increment value, maximum value, minimum value, cycle option, or cache option.

Syntax:  ALTER SEQUENCE sequencename

                                    [INCREMENT BY n]

                                    [{MAXVALUE n | NOMAXVALUE}]

                                    [{MINVALUE n | NOMINVALUE}]

                                    [{CYCLE | NOCYCLE}]

                                    [{CACHE n | NOCACHE}];

Example: ALTER SEQUENCE XXDEPT_S

                                 INCREMENT BY 20

                                 MAXVALUE 999999

                                 NOCACHE

                                 NOCYCLE;

 

Dropping a Sequence :

Remove a sequence from the data dictionary by using the DROP SEQUENCE statement.

Once removed, the sequence can no longer be referenced.

You must be the owner of the sequence or have the DROP ANY SEQUENCE privilege to remove it.

Syntax:

               

    DROP SEQUENCE sequencename;

 

   Example: DROP SEQUENCE XXDEPT_S;

  

   In the syntax:

               sequence is the name of the sequence generator.