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
START WITH specifies the starting value of the Sequence.
INCREMENT BY is the value by which sequence will be incremented.
Products from Amazon.in
Price: Check on Amazon
Price: INR 29,950.00
Price: INR 17,999.00
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
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)
(seq_sam.NEXTVAL, ‘Support’, ‘London’);
View the current value of the SEQUENCE.
SQL> Select seq_sam.CURRVAL