SQL Sequence with example

In this post we can covers what is SQL sequence,how to create SQL sequence and how to drop SQL sequence with examples. The main purpose of sequence is to create a primary key value,which must be unique for each row. That means you can create an auto number field by using sequences.

A sequence is a user defined object that can be shared by multiple users to generate unique values automatically.

Use of sequence:

• It is a time saving object because it can reduce the amount of application code needed to write a sequence-generating routine
• sequence numbers are stored and generated independently of tables. Therefore,same sequence can be used for multiple tables

How to create sequence:

To create sequence we should follow the following syntax

CREATE SEQUENCE sequence_name

[INCREMENT BY n]

[START WITH n]

[{MAXVALUE n} | NO MAXVALUE}]

[{MINVALUE n} |NO MINVALUE}]

[{CYCLE | NOCYCLE}]

[{CACHE n |NOCACHE}];

In the above syntax

sequence_name is the name of the sequence generator

INCREMENT BY n specifies that the value increment or decrement by. If you specify 

negative value then value will be decremented. Where n is integer value or numeric value.

START WITH n specifies that the first value returned by the sequence object

MAXVALUE n specifies that the Maximum value the sequence is generated

MINVALUE n specifies that the Minimum value the sequence is generated

CYCLE: The default is NOCYCLE. It specifies that whether the sequence object should restart when Maximum value or minimum value is reached. If minimum or maximum value exceeded then it throws an error.

CACHE specifies that how many values keep in memory. That means cache sequence values for performance.

Example:

I am creating a sequence named emp to be used for the primary key of the Employee table.

CREATE SEQUENCE  emp

            INCREMENT BY 5

            START WITH 1

            MAXVALUE 1000

            NOCYCLE

            NOCACHE;

sequence is created.

In the above  sequence start with 1,you should remember you can't start from negative values.

After create your  sequence ,it generates sequential numbers for use in your tables.

Now we will create table named as Employee

CREATE TABLE Employee(id number,ename varchar2(20));

Table is created.

Now to refer sequence values by using two Pseudo-columns. They are

1. NEXTVAL 
2. CURRVAL

NEXTVAL is used to returns the next available sequence value. That means it returns unique value every time it is referenced.

For example:

we created a table named as employee,now we will insert a row in employee table.

insert into employee values(emp1.nextval,'&ename');
insert into employee values(emp1.nextval,'lucky'); 
Then it will ask only ename value like as follows:

Enter the value for name: Lucky

Now,one row is inserted
Again press enter  second row like as follows

Enter the value for name: arjun
insert into employee values(emp1.nextval,'&sname');
insert into employee values(emp1.nextval,'arjun'); 

Now, second row is inserted.

Enter the value for name: sai
insert into employee values(emp1.nextval,'&sname');
insert into employee values(emp1.nextval,'sai'); 

1 row is inserted.

Now we will check total rows in a student table like as follows:

select * from student;

Now it will display result like as follows:

Id           ename
1               lucky
6              arjun
11             sai


When your reference sequence.nextval , a new sequence number is generated automatically and the current sequence number is place in the CURRVAL.

CURRVAL is used to obtain the current sequence value. That means it refer to a sequence number that the current user just generated.

If you want check current value then write command like as follows:

select st.currval from dual;

The result will display like as follows:

CURRVAL
------------
    11

NOTE: NEXTVAL must be issued for that sequence before CURRVAL contains a value.

If you want to modify the sequence then do like this:

SQL> alter sequence emp1
                increment by 10
                maxvalue 1500
                  nocache
                  nocycle

sequence is altered.

Now we do insert values into  a employee table

insert into employee values(emp1.nextval,'mahesh');

1 row is inserted.

insert into employee values(emp1.nextval,'pavan');

1 row is inerted

insert into student values(st.nextval,'sravya');

1 row is inserted.

Now we will display the result as follows:

select * from employee;

id       name
--------------
1         lucky
6        arjun
11       sai
21       mahesh
31       pavan
41      sravya


Guidelines  for Modifying the sequence:

• You must be the owner privilege for the sequence
• Only future sequence are effected.
• Some validation is performed

See Also:

Views in SQL with example
SubQuery in SQL with example
GroupBy and Having clause in SQL
TCL Commands In SQL