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.