Sequence

 

An Oracle Sequence is a database object that generates unique numeric values in sequence.

It is commonly used to create primary key values in tables.

Sequences help avoid concurrency issues when multiple users insert records simultaneously

if MAXVALUE is not specified, Oracle sets it as:

• For an ascending sequence: MAXVALUE 10^27 (1 followed by 27 zeros).
• For a descending sequence: MINVALUE is set as the lowest possible number (by default, -10^26).

 

 

CREATE SEQUENCE emp_seq 

START WITH 1                                -- Start from 1

INCREMENT BY 1                          -- Increase by 1

MAXVALUE 1000                          -- Stops at 9999999999

NOCYCLE                                       -- Do not restart after reaching max value 

NOCACHE;                                    -- No pre-allocation of numbers             

 

Using a Sequence:

Instead of manually assigning emp_id, use the sequence’s NEXTVAL.

INSERT INTO employees (emp_id, emp_name)  VALUES (emp_seq.NEXTVAL, 'John Doe');

Get Current Value :

To get the current value without incrementing, use .CURRVAL:

SELECT emp_seq.CURRVAL FROM dual;

Note: CURRVAL can be used only after NEXTVAL has been called in the session.

 

Altering a Sequence:

You can modify an existing sequence using ALTER SEQUENCE:

ALTER SEQUENCE seq_example 

INCREMENT BY 5 

MAXVALUE 1000 

CYCLE;  -- Restart when max value is reached

Dropping a Sequence

To remove a sequence from the database:

DROP SEQUENCE seq_example;

Defaulting Sequence :

From Version 12c sequence can be defaulted.

CREATE TABLE employees (

    id NUMBER DEFAULT seq_example.NEXTVAL PRIMARY KEY,

    name VARCHAR2(100)

);

INSERT INTO employees (name) VALUES ('John Doe');