Sequence

Sequence

• Sequence is a sequential list of unique numbers.
• It is useful for table's column without maximum I/O operations.
• The number of sequence are integers numbers.
• It has mximum 38 digits.
• The sequence store its information in oracle data dictionary "USER_SEQUENCES".
  
• For Example:
• Assume two users are simultenously insert new employee rows in employee table.
• So, the sequence automatically generates the correct value for each other.
• Syntax:

Create sequence [start with integer] [increment by integer] [maxvalue integer | nomaxvalue] [minvalue integer | nominvalue] [cycle | nocycle] [cache integer] [order | noorder]

• Alter sequence Syntax:

alter sequence [increment by integer] [maxvalue integer | nomaxvalue] [minvalue integer | nominvalue] [cycle | nocycle] [cache integer] [order | noorder]

• Example:

create sequence seq_rollno start with 1 maximum value 10 cycle cache 5;

• Example:

alter sequence seq_rollno incremenet by 2 maximum value 100;

• Accessing columns:
• There are special two columns.
• It is also calledd pseudo columns.
• 1. currval: It will return current value of sequence.
• 2. nextval: it increase the value of sequence and return next value.
• Syntax:

.currval
.nextval

• Example:

seq_rollno.currval
seq_rollno.nextval

Insert into student values(seq_rollno.nextval, 'satish');
Insert into student values(seq_rollno.nextval, 'Sarthak');

• How to drop sequence?
• Syntax:

drop sequence ;

• Example:

drop sequence seq_rollno;