SQL Sequences
Posted on December 23, 2009
SQL Sequences Introduction:
The use of sequence is very important in Oracle. A sequence is a database item that generates a sequence of integers. It is a set of integers that are used to create a unique number to act as a primary key.
Sytax of an Oracle Sequence
The syntax for a sequence is:
CREATE SEQUENCE sequence_name
MINVALUE value
MAXVALUE value
START WITH value
INCREMENT BY value
CACHE value;
SQL Sequence Example:
Let us consider a simple example. Suppose we want to create a sequence object called “s”, it starts with 1, the minimum and maximum values are 1 and 100 respectively, it is incremented by 1 and will cache up to 20 values. Then the following command will be used:
CREATE SEQUENCE s_seq
MINVALUE 1
MAXVALUE 100
START WITH 1
INCREMENT BY 1
CACHE 20;
The default value of MAXVALUE option is 999999999999999999999999999. It is the maximum value up to which it is used.
NEXTVAL Operator:
Nextval Operator is used to retrieve the next value in the sequence order.
The general way to use this command is as follows:
s.NEXTVAL
where “s” is the sequence object.
This would retrieve the next value from “s”.
CURRVAL Operator:
Currval Operator is used to retrieve the current value in the sequence order.
The general way to use this command is as follows:
s.CURRVAL
where “s” is the sequence object.
This would retrieve the current value from “s”.
INSERT Statement & Sequences:
Insert statement is used to insert values in the table.
Let us understand this with a simple example:
INSERT INTO s
(s1_id, s1_name)
VALUES
(s.NEXTVAL, ‘hello student’);
The insert statement would insert a new record into the “s” table. The s1_id field would be assigned the next number from the “s” sequence. The s1_name field would be set to ‘hello student’.
Let us take few more examples:
-
Creating a sequence “ t ”
CREATE SEQUENCE t
START WITH 10
INCREMENT BY 5
MINVALUE 10
MAXVALUE 20
CYCLE CACHE 2 ORDER ;
Sequence created.
-
Creating a sequence “ d ” start with 50 increment by 10
CREATE SEQUENCE d
START WITH 50
INCREMENT BY 10 ;
Sequence created.
SELECT statement & Sequences:
Select command is used to select a data from a database and then display it.
Syntax:
SELECT column name
FROM table name;
Let us take an example:
SELECT d.nextval
FROM dhr;
Output will be as follows:
NEXTVAL
———-
50
DROP a Sequence:
A drop statement is used for deleting a sequence. If the sequence is deleted, it will not be restored again.
Syntax:
DROP SEQUENCE name;
For example:
DROP SEQUENCE t;
Sequence dropped.
Note: The sequence “ t ” is dropped and will not be restored again.
Looking for a Fresher Job? Post Your Resume Free !!!
More Articles From "SQL" Category
