Let us learn Sequence in Oracle

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.