SEQUENCE in ORACLE
What is Sequence-
Sequence is a statement in Oracle SQL Command which is used to enable auto-increment feature. Let us understand with an example- when organization wants to store information of Employee in Database Table than first employee will take Employee ID 1 and another Employee will get Employee ID 2 which is nothing but incremented by one of last Employee ID. User wants this feature of increment but he is not interested to put increment value again and again. Hence this concept of increment comes here called Sequence to resolve this kind of problem in SQL Language inside Oracle System where operator do not need to put value of Employee Id again and again. Operator just use Sequence Statement that will provide automatic incremented value to insert at the place or Employee ID.
Syntax of SEQUENCE statement-
CREATE SEQUENCE sequence_name
[ START WITH starting_number ]
[ INCREMENT BY numeric_interval ]
[ MINVALUE min_numeric_value | NOMINVALUE ]
[ MAXVALUE max_numeric_valeu | NOMAXVALUE ]
[ CYCLE | NOCYCLE ]
[ CACHE size_of_cashe | NOCHACHE ]
[ ORDER | NOORDER ] ;
Let s Understand each term used in SEQUENCE statement-
1. CREATE SEQUENCE- This keyword is used to create a Sequence and after Sequence keyword it takes sequence name. This name is used in insert query with nextval function to automatic insert next value or auto increment value. if you want to create your sequence in specific schema than you will have to mention sequence name along with Schema.(example- schema.seq_name).
2. START WITH- This keyword is used to take starting numeric value to specify the starting value of sequence. This is actually the first value of sequence and this is called minimum value in ascending order and Maximum value in Descending Order by default.
3. INCREMENT BY- This keyword is used to illustrate the interval value between two consecutive sequence value. The Default interval value is 1 and the value of interval may have less than 28 digits. Interval must be less than MAXVALUE - MINVALUE. The Interval is Positive if sequence in ascending (1,2,3,4,....) order else Negative in Descending( -1,-2,....) order.
4. MAXVALUE- This keyword is used to specify the Maximum Value for Sequence. If user wants to specify maximum value than write it after the MAXVALUE keyword. Max value must be greater or equal to starting value.
5. NOMAXVALUE- If user not interested to specify maximum value or want to specify automatic maximum value generated by system than user can use NOMAXVALUE keyword.
NOMAXVALUE denotes 10^27 as a maximum value in ascending order and -1 as a Maximum Value in Descending Order. Oracle uses NOMAXVALUE by default.
6. MINVALUE- This keyword is used to specify the Minimum Value for Sequence. If user wants to specify minimum value than write it after the MINVALUE keyword. Min value must be less than or equal to starting value.
7. NOMINVALUE- Oracle take by default NOMINVALUE. This is used to denote 1 in ascending and -10^26 in descending order.
8. CYCLE- This keyword is used to take again minimum value after reaching the LIMIT of sequence.
In ascending order, if limit reached than it generate the minimum value. In descending order, if limit reached than it generate the maximum value.
9. NOCYCLE- This keyword is used to stop generating new sequence value when it reaches its limit. This is used by default.
10. CACHE- This keyword is used to specify the number of sequence values that oracle already allocate or pre allocate and store it for fast accessing. Minimum cache size is 2 and maximum cache size is formulated by given formula-
(ceil ( maxvalue - minvalue )) / abs(increment)
sequence value will be destroyed or lost if any system failure occurred.
11. ORDER- By this keyword you can request oracle to ensure the ordering of the sequence number.
12. NOORDER- This keyword is used to ensure oracle generated order by default.
Example of SEQUENCE-
Now we understood completely about the terms or keyword used in SEQUENCE statement. So we are trying to understand it with an example by creating a basic SEQUENCE in which we use the sequence name as sequence_1, Start with 2, Minimum value 2, maximum value 6, cache size 2, Increment by 2 & using cycle. For this we uses following SEQUENCE syntax-
CREATE SEQUENCE sequence_1
START WITH 2
INCREMENT BY 2
MINVALUE 2
MAXVALUE 6
CACHE 2
CYCLE ;
The above statement will create the sequence after enter command. We can use this created sequence with dual table which is created by default in oracle.
🔑If we want the current value of sequence we will use the CURRVAL function of sequence. Let us
take an example of it with dual table as given below-
SELECT sequence_1.currval from DUAL;
output of above query given below-
CURRVAL
------------------
2
In our case e assumed that NEXTVAL function called once otherwise there will be an error which illustrate that sequence_1.CURRVAL is not defined yet in this session.
🔑 If we want the next value of sequence we will use the NEXTVAL function of sequence. Let us take
an example of it with dual table as given below-
SELECT sequence_1.nextval from dual;
output of above query given below-
NEXTVAL
------------------
4
🔑 Now we assume that we just created the sequence and we want to check how increment and cycle
will work with the help of dual table given below-
SELECT sequence_1.nextval from dual CONNECT BY LEVEL<=4;
output of above query given below-
NEXTVAL
------------------
2
4
6
2
As we can see that the next value of sequence again repeated value 2 because we are using cycle property of sequence statement.
Use of SEQUENCE with TABLE like STUDENT-
Now we are trying to create a table called student and also create a sequence and will insert value into table by using sequence.nextval function.
syntax to create student table-
CREATE TABLE student (
RollNo int primary key,
Name varchar(20),
Mobile NUMBER(10)
);
syntax to create sequence-
CREATE SEQUENCE sequence_1
START WITH 1
INCREMENT BY 1
MINVALUE 1
MAXVALUE 4
CACHE 2
CYCLE ;
Now we have done table and sequence creation so will will insert the value inside table using insert into statement and sequence_1.nextval function.
INSERT INTO student VALUES ( sequence_1.nextval, "ABC", 1234567890);
The Above query will insert RollNo as 1 because sequence_1.nextval called first time and the start value of sequence_1 is 1. When this query execute again it will insert RollNo as 2 because we used INCREMENT BY 1 inside SEQUENCE. When RollNo becomes 4 and if we again execute this query than RollNo will be 1 because of Cycle property that will give an error due to RollNo is primary key which can't take duplicate value.
Overall Output with tested query given below-
 |
| Sequence Example |
No comments:
Post a Comment