SEQUENCE in SQL

In this tutorial, We will learn about the SEQUENCE in SQL.

Sequence is a feature supported by some database systems to produce unique values on demand.

 

  • The sequence in Automatically generated the unique number.

  • It is a shareable object in DB.

  • We can use SEQUENCE to create PRIMARY KEY in the database.

 

Syntax of Sequence in SQL:

SQL > CREATE Sequence sequence-name

           START WITH initial-value

           INCREMENT BY increment-value

           MAXVALUE maximum-value

           MINVALUE minimum-value

           CYCLE|NOCYCLE ;

 

START WITH specifies the starting value of the Sequence.

INCREMENT BY is the value by which sequence will be incremented.

MAX VALUE specifies the maximum value until which sequence will increment itself.

CYCLE specifies that if the maximum value exceeds the set limit, the sequence will restart its cycle from the beginning.

No CYCLE specifies that if sequence exceeds max value an error will be thrown.

 

SEQUENCE in SQL with Example

First Let’s create a SEQUENCE and then we will see, How can we use SEQUENCE.

SQL > CREATE Sequence seq_sam

           start with 1

           increment by 1

           maxvalue 999

           cycle ;

 

Now let’s see, How can we use  the above sequence.

Use of SEQUENCE while updating the table.

SQL > INSERT INTO Departments 

                   (Dept_id, Dept_name, Location)

          VALUES

                   (seq_sam.NEXTVAL, ‘Support’, ‘London’);

 

View the current value of the SEQUENCE.

SQL> Select seq_sam.CURRVAL 

          From dual;

 

0 Comment

Leave a Reply