Let us learn Sequence in Oracle

Niladri.Biswas
Posted by in Oracle category on for Beginner level | Points: 250 | Views : 3978 red flag

In this article we will learn Oracle's SEQUENCE

Introduction

In Oracle, SEQUENCE generates auto number field in tables.

Syntax

CREATE SEQUENCE sequence_name
    MINVALUE value
    MAXVALUE value
    START WITH value
    INCREMENT BY value
    CACHE value;

Straight to Experiment

We can create a sequence as under

SQL> CREATE SEQUENCE MySequence
  2  MINVALUE 1
  3  MAXVALUE 100
  4  START WITH 1
  5  INCREMENT BY 1;

Sequence created.

So our sequence has been created whose minimum value is 1 and maxvalue is 100. The sequence will start from 1 and will be incremented by 1.

The max value for a sequence will be 999999999999999999999999999

We can always query the User_Sequences table to find the information about the sequence

SQL> Select * from user_sequences;

SEQUENCE_NAME                   MIN_VALUE  MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER
------------------------------ ---------- ---------- ------------ - - ---------- -----------
MYSEQUENCE                              1        100            1 N N         20          21

Now we will create a create a table where we will use our sequence to generate auto number

Create table tblTestSequence(ID int, Name Varchar2(50));
Insert Into tblTestSequence Values(MySequence.NextVal,'Name1');
Insert Into tblTestSequence Values(MySequence.NextVal,'Name2');
SQL> Select * From tblTestSequence;

        ID NAME
---------- --------------------------
         1 Name1
         2 Name2

The NextVal fetches the the next available value in the sequence for use while the CurrVal beings the current one.

SQL> Select MySequence.CurrVal From dual;

   CURRVAL
----------
         2

We can drop a sequence by using the Drop Sequence statement

SQL> Drop Sequence MySequence;

Sequence dropped.

Conclusion

So in this article, we have seen how Oracle's Sequence works.Hope this will be helpful.Thanks for reading.

Page copy protected against web site content infringement by Copyscape

About the Author

Niladri.Biswas
Full Name: Niladri Biswas
Member Level: Platinum
Member Status: Member
Member Since: 10/25/2010 11:04:24 AM
Country: India
Best Regards, Niladri Biswas
http://www.dotnetfunda.com
Technical Lead at HCL Technologies

Login to vote for this post.

Comments or Responses

Login to post response

Comment using Facebook(Author doesn't get notification)