Sequences

The quickest way to retrieve data from a table is to have a column in the table whose data uniquely identifies a row. By using this column and a specific value in the where condition of a select statement the oracle engine will be able to identify and retrieve the row fastest.
Oracle provides an object called a sequence that can generate numeric values. The value generated can have a maximum of 38 digits. A sequence can be defined to

• Generate numbers in ascending or descending
• Provide intervals between numbers
• Caching of sequence numbers in memory etc

A sequence is an independent object and can be used with any table that requires in output.
Creating Sequences
The minimum information required for generating numbers using sequence is

• The starting number
• The maximum number that can be generated by a sequence
• The increment value for generating the next number

Syntax:- CREATE SEQUENCE sequencename
[ INCREMENT BY intvalue
START WITH value
MAXVALUE val / NOMAXVALUE
MINVALUE val/NOMINVALUE
CYCLE/NOCYCLE
CACHE value /NOCACHE
ORDER/NOORDER    ]
NEXTVAL: Nextval will return initial values of the sequence, when referred to, for the first time. Later reference to nextval  will increment the sequence value by increment by clause and return the new value.
CURRVAL: currval returns the current value of the sequence which is the value returned by the last reference to nextval.

Ex:-     create sequence myseqence increment by 1 start with 1
maxvalue 100 cycle;
create table payslip(eno number(5),na varchar2(20),sal number(5));
insert into payslip(myseqence.nextval,’prasad’,3200);
insert into payslip(myseqence.nextval,’vamsi’,200);
insert into payslip(myseqence.nextval,’bvnr’,2300);
select * from payslip;
select myseq.currval from dual;

Altering a Sequence
            A sequence once created can be altered. This is achieved by using the following syntax.
Syntax:- alter sequence seq-name all options of the sequence;
Ex:- alter sequence myseq increment by 2 cache 20;
        insert into payslip  values(myseq.nextval,'vision',3000);
        select * from payslip;
Note:- cache option specifies how many values of a sequence oracle pre-allocates and keeps in memory for faster access . The min value for this parameter is 2.

Dropping a Sequence
 This command removes the sequence from the database
Syn:- drop sequence seq-name

For
Online Classes

Contact Us: +919885348743