SEQUENCES

A sequence is used to generate numbers in sequence. You can use sequences to insert unique values in Primary Key and Unique Key columns of tables. To create a sequence give the CREATE SEQUENCE statement.

CREATING SEQUENCES

create sequence bills
     start with 1
     increment by 1
     minvalue 1
     maxvalue 100
     cycle
     cache 10';

The above statement creates a sequence bills it will start with 1 and increment by 1. It’s maxvalue is 100 i.e. after 100 numbers are generated it will stop if you say NOCYCLE, otherwise if you mention CYCLE then again it will start with no. 1. You can also specify NOMAXVALUE in that case the sequence will generate infinite numbers.

The CACHE option is used to cache sequence numbers in System Global Area (SGA). If you say CACHE 10 then Oracle will cache next 10 numbers in SGA. If you access a sequence number then oracle will first try to get the number from cache, if it is not found then it reads the next number from disk. Since reading the disk is time consuming rather than reading from SGA it is always recommended to cache sequence numbers in SGA. If you say NOCACHE then Oracle will not cache any numbers in SGA and every time you access the sequence number it reads the number from disk.

Accessing Sequence Numbers.

To generate Sequence Numbers you can use NEXTVAL and CURRVAL for example to get the next sequence number of bills sequence type the following command.

Select bills.nextval from dual;

BILLS
-----
1

NEXTVAL gives the next number in sequence. Whereas, CURRVAL returns the current number of the sequence. This is very handy in situations where you have insert records in Master Detail tables. For example to insert a record in SALES master table and SALES_DETAILS detail table.

insert into sales (billno,custname,amt)
values (bills.nextval,’Sami’,2300);

insert into sales_details (billno,itemname,qty,rate) values
(bills.currval,’Onida’,10,13400);

Sequences are usually used as DEFAULT Values for table columns to automatically insert unique numbers.
For Example,

create table invoices (invoice_no number(10) default bills.nextval,
       invoice_date date default sysdate,
       customer varchar2(100),
       invoice_amt number(12,2));

Now whenever you insert rows into invoices table ommiting invoice_no as follows

insert into invoices (customer,invoice_amt) values ('A to Z Traders',5000);

Oracle will insert invoice_no from bills sequence

ALTERING SEQUENCES

To alter sequences use ALTER SEQUENCE statement. For example to alter the bill sequence MAXVALUE give the following command.

ALTER SEQUENCE BILLS
MAXVALUE 200;

Except Starting Value, you can alter any other parameter of a sequence. To change START WITH parameter you have to drop and recreate the sequence.

DROPPING SEQUENCES

To drop sequences use DROP SEQUENCE command. For example to drop bills sequence give the following statement

drop sequence bills;

Listing Information About Sequences

To see how many sequences are there in your schema and what are there settings give the following command.

select * from user_sequences;

Using Sequences in Oracle (Auto Increment Feature)