Sequence in SQL

Sequence

A sequence is auto generated Number.
–- Sequence Can automatically generate unique numbers
–- Sequence is used to create a primary key.
–- Sequence Speeds up the efficiency of accessing sequence values when cached in memory

Creating Sequence:

Syntax for creating sequence.


CREATE SEQUENCE sequence_name
[INCREMENT BY n]
[START WITH n]
[{MAXVALUE n| NOMAXVALUE}]
[{MINVALUE n| NOMINVALUE}]
[{CYCLE | NOCYCLE}]
[{CACHE n| NOCACHE}];

Example:

CREATE SEQUENCE dept_Number_seq
INCREMENT BY 10
START WITH 500
MAXVALUE 9999
NOCACHE
NOCYCLE;

Method to get Sequence Value

NEXTVAL

  • returns the next available sequence value.
  • It returns a unique value every time it is referenced, even for different users.

CURRVAL

  • obtains the current sequence value.

Viewing the current value for sequence.
i.e. Select dept_Number_seq.CURRVAl from dual;

Using Sequence into Query:

INSERT INTO departments(department_id,department_name, location_id)
                VALUES (dept_deptid_seq.NEXTVAL,'Tech Support', 2500);

Caching the Sequence

  • Caching sequence values in memory gives faster access to those values.
  • Gaps in sequence values can occur when:
  • –– A rollback occurs
    –– The system crashes
    –– A sequence is used in another table

Modifying a Sequence

You can Change the increment value, maximum value, minimum value, cycle option, or cache option:

ALTER SEQUENCE dept_Number_seq
INCREMENT BY 1000
MAXVALUE 999999
NOCACHE
NOCYCLE;

Leave a Reply

Your email address will not be published. Required fields are marked *