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.
create sequence bills
start with 1
increment by 1
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.
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;
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)
insert into sales_details (billno,itemname,qty,rate) values
Sequences are usually used as DEFAULT Values for table columns to
automatically insert unique numbers.
create table invoices (invoice_no number(10) default
invoice_date date default sysdate,
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
To alter sequences use ALTER SEQUENCE statement. For example to alter the bill sequence MAXVALUE give the following command.
ALTER SEQUENCE BILLS
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.
To drop sequences use DROP SEQUENCE command. For example to drop bills sequence give the following statement
drop sequence bills;
To see how many sequences are there in your schema and what are there settings give the following command.
select * from user_sequences;