SQL tutorial 61: SEQUENCE in Oracle Database By Manish Sharma RebellionRider


What’s up internet?
I am Manish from RebellionRider.com and I am back once again with another SQL tutorial
and today in this tutorial we will learn about sequences in Oracle Database.
So without further ado let’s start the tutorial. A Sequence is a database object which generates
integer sequence. We generally use it for populating numeric Primary Key columns.
On this note let’s move ahead and see how we can create a sequence. In order to create
a sequence we use create sequence DDL statement. Let’s take a look at CREATE SEQUENCE DDL
CREATE SEQUENCE sequence_name [START WITH start_num]
[INCREMENT BY increment_num] [MAXVALUE maximum_num | NOMAXVALUE]
[MINVALUE minimum_num | NOMINVALUE] [CACHE cache_num | NOCACHE]
[CYCLE | NOCYCLE] [ORDER | NOORDER];
Create Sequence DDL statement starts with CREATE and SEQUENCE, both of which are Oracle
Reserved keyword followed by the name of the sequence which is purely user defined meaning
you give any name of your choice to your sequence. Next we have few attributes of the sequence.
First attribute is START WITH
Here you have to specify a numeric value from which you want your sequence to start. Whatever
number you specify will be the first number generated by your sequence.
Next attribute is INCREMENT BY This attribute also takes a numeric value
to increment the sequence by. The number that you specify here will serve as the interval
between sequence numbers. The value for INCREMENT BY cannot be 0 but it can be any positive
or negative value. If this value is negative, then the sequence descends. If the value is
positive, then the sequence ascends. If you omit this clause, then the interval defaults
to 1. Next attribute is MAXVALUE or NOMAXVALUE
Using these attributes you can set the maximum upper bound for your sequence. Always remember
MAXVALUE must be equal to or greater than START WITH and must be greater than MINVALUE
attribute. In case you don’t want to set the MAXVALUE for your sequence then you can
use NOMAXVALUE attribute. Similar to MAXVALUE we use MINVALUE attribute
to set the lower bound of our sequence. As a value this attribute also accepts the numeric
value and should be less than or equal to START WITH as well as less than MAXVALUE.
In case you don’t want to set the lower bound for your sequence then you can use NOMINVALUE
attribute instead. Then we have CACHE or NOCACHE attribute
As value of cache attribute you specify the number of integers to keep in memory. The
default number of integers to cache is 20. The minimum number of integers that may be
cached is 2. The maximum integers that may be cached is determined by this formula
CEIL(maximum_num -minimum_num)/ABS(increment_num). Specify NOCACHE to indicate that values of
the sequence are not pre-allocated. If you omit both CACHE and NOCACHE, the database
caches 20 sequence numbers by default. Next we have CYCLE or NOCYCLE flags
CYCLE and NOCYCLE are two flags which you have to set. If you set the flag on cycle
then your sequence continues to generate values after reaching either its maximum or minimum
value. You specify NOCYCLE flag when you do not want your sequence to generate more values
after reaching its maximum or minimum value. If in case you omit both these flags then
by default oracle engine will set the flag on NOCYCLE.
At last we have two more flags which are ORDER and NOORDER. ORDER Flag guarantees that sequence
numbers are generated in order of request. This clause is useful if you are using the
sequence numbers as timestamps. Guaranteeing order is usually not important for sequences
that are used to generate primary keys. Set the flag on NOORDER if you do not want
to guarantee that the sequence numbers are generated in order of request. NOORDER is
the default flag in case you omit either of them.
Hope syntax is clear to you. Now let’s try some examples.
Let’s create a sequence by the name of sq_demo CREATE SEQUENCE sq_demo1
START WITH 1 INCREMENT BY 2
MAXVALUE 10 MINVALUE 1
CACHE 3 CYCLE
ORDER; This is a simple sequence with the name of
sq_demo which is starting with 1 and the interval between every sequence is 2. The Max value
for this sequence is 10 and Min value is 1 and this sequence will cache 3 integers at
a time and will cycle after reaching the maximum value which is 10. Also I have set ORDER FLAG
which means generation of the number will generate in guaranteed order.
Let’s execute, Sequence sq_demo created. Ok let me close this script output ok there
are Few things which you should know:
1. You can specify any of these attributes and flags in any order. Means order of these
flags is not fixed. 2. All these attributes and flags are optional.
If you omit all of them then oracle engine will create a default sequence for you. Let’s
see an example CREATE SEQUENCE sq_demo02;
Let’s execute sequence sq_demo01 is created So now that you have learnt how to create
a sequence next step is how you can use a sequence.
To use a sequence we use NEXTVAL and CURRVAL. Both these are pseudo columns of a sequence
using which we can retrieve next value and current value of a sequence. NEXTVAL column
returns the next value of the sequence as well as initialize the sequence whereas CURRVAL
column will return the current value of the sequence.
Let’s see how. Let’s try to retrieve the current value
from our sequence SQ_DEMO which we just created. But before that let me close this script output
let me also make some space so that you people can see things clearly ok let’s write the
query to retrieve current value from our sequence SQ_Demo
SELECT sq_demo.currval FROM dual; Let’s execute and we got an error. This
is because Before using any sequence it’s mandatory
to initialize it first. If you will try to retrieve current value without initializing
it then it will give you an error. We use NEXTVAL pseudo column to initialize
a sequence as well as to retrieve next value of the sequence.
Let’s me show you how you can initialize a sequence using NEXTVAL pseudo column but
before that let me close this query result SELECT sq_demo.NEXTVAL FROM dual;
Let’s execute As you can see the value returned from this query is the value which you have
assigned to your START WITH attribute. Let me adjust my output panel
Every time you execute this SELECT DML, the sequence will generate the next integer in
the row. Which means that if I will execute the same SELETC DML then next integer will
be 3 because we have specified the interval between the sequence as 2 in INCREMENT BY
attribute. Let’s execute same select statement once
again Value returned is 3 Now once again try executing the CURRVAL query
which gave us an error last time and see what happens this time.
Let’s execute As you can see this query executed successfully
this time and returned the current value of the sequence.
This means that after creating a sequence first you have to execute NEXVAL query to
initialize it and then you can use it. Next we will see how we can populate a primary
key column using sequence. For the demonstration I have created a table
by the name of demo which has only two columns. First is demo_id which is our primary key
column and can hold numeric data as its data type is NUMBER. Next is demo_name which is
capable of holding variable character string as its data type is VARCHAR2.
Now we will insert the rows into this table and populate the primary key column using
the sequence which we just created let’s see how.
You can refer my tutorial __ and __ Let’s write the INSERT DML
INSERT INTO demo VALUES(sq_demo.nextval, ‘xyz’); Let’s execute
1 row inserted. You can execute this INSERT DML as many times as you want until sequence
reaches its highest value. Before winding up this tutorial let’s see
how we can delete a sequence from the schema. To Delete any sequence we use DROP DDL statement.
Say you want to delete the Sequence sq_demo which we just created for that, the statement
will be DROP SEQUENCE sq_demo;
Let’s execute Sequence dropped.
You can read my blog on the same topic for more in depth knowledge such as how we can
change the definition of a sequence after creating it and many more…
That’s it for today. Please hit the like button if you like this video and leave a
comment or tweet me your feedback and Don’t forget to hit that big red button and subscribe
to my channel.. Will see you soon with another SQL Tutorial.
Till then take care. This is Manish from RebellionRider.

Daniel Ostrander

Related Posts

Leave a Reply

Your email address will not be published. Required fields are marked *