Aim :
To create and use the sequences for auto-numbering.
Description:
The Oracle SEQUENCE function allows you to create auto-numbering fields by using sequences. An Oracle sequence is an object that is used to generate incrementing or decrementing numbers.
Full Create Sequence Syntax
SYNTAX:
CREATE SEQUENCE <sequence_name>
MINVALUE value
MAXVALUE value
START WITH value
INCREMENT BY value
CACHE value
EXAMPLE :
CREATE SEQUENCE user_seq
MINVALUE 1
MAXVALUE 20000
START WITH 1
INCREMENT BY 1
CACHE 100;
The example above will create a sequence object called user_seq Because of the ‘START WITH 1’ parameter the first number in the sequence will be ‘1’.
The ‘MAXVALUE 20000’ parameter tells Oracle to stop generating sequence numbers at 20,000.
The ‘INCREMENT BY 1’ parameter will cause the sequence numbers to increment by 1 (i.e. 2,3,4, and so on.).
The last line, ‘CACHE 100’ tells Oracle to pre-generate or cache the next 100 sequence values and store them in system memory for increased performance. If the database fails (crashes), the sequence numbers are lost, and when the application is restarted, a gap may occur in the sequence numbers. To prevent this possibility you can specify NOCACHE, which defers the caching of sequence numbers in memory. Using NOCACHE may result in slightly reduced performance, but it will prevent any gaps from appearing in the sequence should a database failure take place. In general, Oracle recommends caching sequence numbers and not using the NOCACHE parameter unless the continuity of sequence numbers is considered a critical factor.
EXAMPLE:
SQL> create sequence user_seq minvalue 1 maxvalue 5 start with 1 increment by 1 cache 5;
SQL> select user_seq.nextval from dual;
NEXTVAL
———-
1
SQL> /
NEXTVAL
———-
2
SQL> /
NEXTVAL
———-
3
SQL> /
NEXTVAL
———-
4
SQL> /
NEXTVAL
———-
5
Create Sequence Simplest Form
SYNTAX:
create sequence <sequence_name>;
Simple Auto number With Sequence:
INSERT INTO <table_name>
(<column_name>)
VALUES
(<sequence_name>.NEXTVAL);
EXAMPLE:
SQL>create table empp( eid number(4),ename varchar2(40),eaddress varchar2(30));
SQL>create sequence empp_seq;
SQL> desc empp;
Name
————-
EID
ENAME
EADDRESS
SQL>insert into empp(eid,ename,eaddress) values(empp_seq.nextval, ‘anitha’, ‘chennai’);
SQL>insert into empp(eid,ename,eaddress) values(empp_seq.nextval, ‘&ename’, ‘&eaddress’);
sql> select * from empp;
eid ename eaddress
———- ——————- ————————
1 anitha chennai
2 banu mumbai
3 jazz delhi
4 arun kerala
5 jose bangalore
6 madhu chennai
7 devi calicut
8 uma krishnagiri
9 geetha chennai
10 kala mudurai
sql> insert into empp(eid,ename,eaddress) values(empp_seq.nextval, ‘kavitha’, ‘chennai’);
insert into empp(eid,ename,eaddress) values(empp_seq.nextval, ‘kavitha’, ‘chennai’)
*
ERROR at line 1:
ORA-08004: sequence EMPP_SEQ.NEXTVAL exceeds MAXVALUE and cannot be instantiated
Alter Sequence:
ALTER SEQUENCE <sequence_name> INCREMENT BY <integer>;
Example:
SQL> alter sequence campus_seq increment by 20;
SQL> insert into campus (cid,campus_name,campus_address) values (campus_seq.nextval, ‘civil block’, ‘rmd engg college’);
SQL> select * from campus;
cid campus_name campus_address
———- ————————- —————————-
1 cse block rmd engg college
2 ece block rmd engg college
3 eee block rmd engg college
23 civil block rmd engg college
Drop Sequence:
DROP SEQUENCE <sequence_name>;
Example:
SQL> drop sequence campus_seq;
Result:
Thus the auto-numbering feature is executed using sequences.
