Oracle/PLSQL Sequence

Description:

Sequence is a database object from which multiple users may generate unique integers.
You can use sequences to automatically generate Primary Key Values.

Prerequisites :

To create a sequence in your own schema,you must have CREATE SEQUENCE system privilege.

To create a sequence in another user's schema,you must have CREATE ANY SEQUENCE system privilege.

Syntax:

CREATE SEQUENCE sequence_name

MINVALUE value

MAXVALUE value

START WITH value

INCREMENT BY value

CACHE value;

Example:

The following statement creates the sequence 

CREATE SEQUENCE seq_empid

MINVALUE 1

MAXVALUE 999999999999999999999999999

START WITH 1

INCREMENT BY 1

CACHE 20;

• The first sequence number that it would use is 1 and each subsequent number would increment by 1(i.e..2,3,4,..).It will values for performance.

• If you omit the MAXVALUE when creating the Sequence,it will automatically default to :

    MAXVALUE 999999999999999999999999999

To retrieve the next value in the sequence order, you need to use nextval.

Syntax:

SEQUNCENAME.NEXTVAL;

This would retrieve the next value from seq_empid. The nextval statement needs to be used in a SQL statement. For example:

INSERT INTO emp

(EmpID, emp_name)

VALUES

(seq_empid.NEXTVAL, 'JOKER');

The EmpIDfield would be assigned the next number from the seq_empid sequence.

Drop Sequence:

To remove the sequence from the database the syntax is give below:

DROP SEQUENCE sequence_name;

For example:

DROP SEQUENCE seq_empid;