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.