Using Sequence in Oracle 11g

Using Sequence in Oracle 

              Sequence is an Auto increment value. Unlike MySQL/SQL-Server Sequence is an Independent Object(i.e not associated with any table) .Mainly used in PL/SQL or Used as a Primary Key value .

Simple Syntax:   Create Sequence  Sequence name;

Ex1:-  create sequence seq1EmpNo;

 Ex2:- create sequence seq2EmpNo start with 9000;

Ex3:-Create sequence seq3EmpNo start with 9000 increment by -1 maxvalue 12000 minvalue 8000;

Ex4- create sequence seq4Empno order;

Ex5:-create sequence seq5Empno cache 28;

Using Sequence in Table Insert/Update

insert into emp values(seq2EmpNo.nextval,'peter','clerk',7782,'23-jan-82',1200,null,10);

Output:

9003 peter      clerk           7782 23-JAN-82       1200

If User wants to see the above sequences ,Query  User_Sequences table.

             select * from User_Sequences;

SEQUENCE_NAME                   MIN_VALUE  MAX_VALUE INCREMENT_BY C O CACHE_SIZE
------------------------------ ---------- ---------- ------------ - - ----------
SEQ1EMPNO                               1 1.0000E+28            1 N N         20
SEQ2EMPNO                               1 1.0000E+28            1 N N         20
SEQ3EMPNO                            8000      12000           -1 N N         20
SEQ4EMPNO                               1 1.0000E+28            1 N Y         20
SEQ5EMPNO                               1 1.0000E+28            1 N N         28

Tags:Using Sequence in Oracle 11g,Auto increment in Oracle table,Sequence as a Primary key,Query user_sequences table, Alter sequence, using sequence in insert command.