Sequence is an object which is used to generate numbers.
Syntax
Create sequence <seq_name>
Start with < value >
Increment by < value >;
Ex
Create sequence seq1
Start with 1
Increment by 1;
In the above example, name of the sequence is seq1. It generated numbers 1,2,3..so on
How can we use the sequence?
I want to create a table student18, and insert 5 rows into it.
student18
sno
sname
marks
1
arun
40
2
vijay
65
3
amit
91
4
karthik
98
5
vishal
77
Observe, the column sno, the values are 1,2,3,4,5.
Instead of hard coding the values, in the insert command, I can use the sequence seq1 which we have created.
Note
Nextval is a pseudo column which is used to generate the numbers.
Ex 1
Create table student18 ( sno number(3),
Sname varchar2(10),
Marks number(3));
Insert into student18 values ( seq1.nextval, arun, 40 );
Insert into student18 values ( seq1.nextval, vijay, 65 );
Insert into student18 values ( seq1.nextval, amit, 91 );
Insert into student18 values ( seq1.nextval, karthik, 98 );
Insert into student18 values ( seq1.nextval, vishal, 77 );
In the above insert commands, we are not hard coding the values of the sno column.
We are using <seq_name>.nextval , it will generate the number.
Note
Currval is a pseudo column which is used to know the latest number generated.
Select seq1.currval from dual; -- 5
So, when ever we want numbers to generated automatically, we use sequence.
Like credit card numbers, mobile numbers, sim card numbers, bank account numbers etc;
Lets look at few more examples
Ex 2
Create sequence seq2
start with 1000
increment by 1
maxvalue 5000;
We can restrict sequence to a maximum number by using maxvalue option.
Ex 3
Create sequence seq3
Start with 1
Increment by 1
Maxvalue 7000
Cycle;
After sequence reaching maxvalue, it again starts generating number 1,2, so on
Sequence with cycle option, cannot be used for primary key columns as it may generate duplicate values.
Query to see list of all the sequences
Select sequence_name from user_sequences;
If you do not want the sequence, we can drop it.
Syntax
Drop sequence < seq_name >;
Ex
Drop sequence seq1;
Imp
There are two pseudo columns, related to sequences.
Nextval
Currval;
Nextval will generate the next number.
Currval will return the latest number generated.