Oracle – Sequence

In Oracle, you can create an auto-number field by using sequences. A sequence is an object in Oracle that is used to generate a number sequence. This can be useful when you need to create a unique number to act as a primary key.

Syntax

Create Sequence

CREATE SEQUENCE sequence_name
START WITH initial_value
INCREMENT BY increment_value
MINVALUE minimum value
MAXVALUE maximum value
CYCLE | NOCYCLE
CACHE value | NOCACHE
ORDER value | NOORDER;
  • sequence_name: Name of the sequence.
  • initial_value: starting value from where the sequence starts. Initial_value should be greater than or equal to minimum value and less than equal to maximum value.
  • increment_value: Value by which sequence will increment itself. Increment_value can be positive or negative.
  • minimum_value: Minimum value of the sequence.
  • maximum_value: Maximum value of the sequence.
  • cycle: When sequence reaches its set_limit it starts from beginning.
  • nocycle: An exception will be thrown if sequence exceeds its max_value.
  • cache: This clause directs Oracle server to cache ā€™nā€™ values of a sequence. Its value is 20 by default.
  • nocache: It is the default behavior of the sequence.
  • order: It ensures that sequence values are generated in order. Generally, it is least usable clause in sequence definition but they are helpful in applications which use sequence as timestamp value.
  • noorder: It is the default behavior of the sequence.

Alter Sequence

ALTER SEQUENCE sequence_name -- You Can Alter any (one) of the following.. Or all...
INCREMENT BY n
MAXVALUE n
MINVALUE n
CYCLE | NOCYCLE
CACHE n | NOCACHE
ORDER n | NOORDER;

Drop Sequence

DROP SEQUENCE sequence_name;

Sequence Usage

-- Next Value (Sequence incremented to next value)
-- ===============================================
-- sequence_name.NEXTVAL
SELECT sequence_name.NEXTVAL FROM dual;

-- Current Value (Showing current incremented value of sequence)
-- =============================================================
-- sequence_name.CURRVAL
SELECT sequence_name.CURRVAL FROM dual;

Sequence Table

SELECT * FROM USER_SEQUENCES WHERE SEQUENCE_NAME = UPPER('sequence_name');

SELECT * FROM ALL_SEQUENCES WHERE SEQUENCE_NAME = UPPER('sequence_name');

Examples

Prerequisite

DROP TABLE emps;
CREATE TABLE emps AS (SELECT employee_id AS id, first_name AS name, salary FROM employees WHERE 1=2);

Example 1

-- Sequence Creation
-- ==================
CREATE SEQUENCE emps_id_seq
START WITH 1
INCREMENT BY 1
MINVALUE 1
MAXVALUE 9
NOCYCLE;

-- Inserting Values
-- =================
INSERT INTO emps VALUES(emps_id_seq.NEXTVAL, 'Stan Lee'   , 50000);
INSERT INTO emps VALUES(emps_id_seq.NEXTVAL, 'Thor'       , 45000);
INSERT INTO emps VALUES(emps_id_seq.NEXTVAL, 'Jane Foster', 40000);
INSERT INTO emps VALUES(emps_id_seq.NEXTVAL, 'Mary Jane'  , 35000);
INSERT INTO emps VALUES(emps_id_seq.NEXTVAL, 'Spider man' , 30000);
INSERT INTO emps VALUES(emps_id_seq.NEXTVAL, 'Stark'      , 25000);
INSERT INTO emps VALUES(emps_id_seq.NEXTVAL, 'Pepper'     , 20000);
INSERT INTO emps VALUES(emps_id_seq.NEXTVAL, 'Oliver'     , 15000);
INSERT INTO emps VALUES(emps_id_seq.NEXTVAL, 'Overwatch'  , 10000);
INSERT INTO emps VALUES(emps_id_seq.NEXTVAL, 'Ethan Hunt' , 5000);
-- ORA-08004: sequence EMPS_ID_SEQ.NEXTVAL exceeds MAXVALUE and cannot be instantiated

-- Checking 
-- =========
SELECT * FROM emps;
SELECT emps_id_seq.CURRVAL FROM dual;

-- Altering Max Value
-- ===================
ALTER SEQUENCE emps_id_seq
MAXVALUE 10;

-- Inserting Again
-- ================
INSERT INTO emps VALUES(emps_id_seq.NEXTVAL, 'Ethan Hunt' , 5000);

-- Dropping Sequence & Clearing Table
-- ===================================
DROP SEQUENCE emps_id_seq;
TRUNCATE TABLE emps;

Example 2

-- Sequence Creation
-- ==================
CREATE SEQUENCE emps_id_seq
START WITH 1
INCREMENT BY 1
MINVALUE 1
MAXVALUE 21 -- This Value should be greater than 20 to use cycle.
CYCLE;

-- Inserting Values
-- =================
INSERT INTO emps VALUES(emps_id_seq.NEXTVAL, 'Stan Lee'   , 50000);
INSERT INTO emps VALUES(emps_id_seq.NEXTVAL, 'Thor'       , 45000);
INSERT INTO emps VALUES(emps_id_seq.NEXTVAL, 'Jane Foster', 40000);
INSERT INTO emps VALUES(emps_id_seq.NEXTVAL, 'Mary Jane'  , 35000);
INSERT INTO emps VALUES(emps_id_seq.NEXTVAL, 'Spidarman'  , 30000);
INSERT INTO emps VALUES(emps_id_seq.NEXTVAL, 'Stark'      , 25000);
INSERT INTO emps VALUES(emps_id_seq.NEXTVAL, 'Pepper'     , 20000);
INSERT INTO emps VALUES(emps_id_seq.NEXTVAL, 'Oliver'     , 15000);
INSERT INTO emps VALUES(emps_id_seq.NEXTVAL, 'Captain America', 10001);
INSERT INTO emps VALUES(emps_id_seq.NEXTVAL, 'Captain Marvel' , 10002);
INSERT INTO emps VALUES(emps_id_seq.NEXTVAL, 'Wonder Women'   , 10003);
INSERT INTO emps VALUES(emps_id_seq.NEXTVAL, 'Flash'          , 10004);
INSERT INTO emps VALUES(emps_id_seq.NEXTVAL, 'Batman'         , 10005);
INSERT INTO emps VALUES(emps_id_seq.NEXTVAL, 'Deadpool'       , 10006);
INSERT INTO emps VALUES(emps_id_seq.NEXTVAL, 'Superman'       , 10007);
INSERT INTO emps VALUES(emps_id_seq.NEXTVAL, 'Ant Man'        , 10008);
INSERT INTO emps VALUES(emps_id_seq.NEXTVAL, 'Daredevil'      , 10009);
INSERT INTO emps VALUES(emps_id_seq.NEXTVAL, 'Electra'        , 10010);
INSERT INTO emps VALUES(emps_id_seq.NEXTVAL, 'Watchmen'       , 10011);
INSERT INTO emps VALUES(emps_id_seq.NEXTVAL, 'Catwomen'       , 10012);
INSERT INTO emps VALUES(emps_id_seq.NEXTVAL, 'Vibe'           , 10013);
INSERT INTO emps VALUES(emps_id_seq.NEXTVAL, 'killer Frost'   , 5000); -- Starting Again From 1
INSERT INTO emps VALUES(emps_id_seq.NEXTVAL, 'Ethan Hunt'     , 5000);

-- Checking 
-- =========
SELECT * FROM emps;
SELECT emps_id_seq.CURRVAL FROM dual;

-- Dropping Sequence & Clearing Table
-- ===================================
DROP SEQUENCE emps_id_seq;
TRUNCATE TABLE emps;
DROP TABLE emps;

Example 3 – Primary Key Auto Increment (Up to 11g)

-- Creating Table
-- ===============
CREATE TABLE invoices(
  id NUMBER PRIMARY KEY,
  inv_name VARCHAR2(25)
);

-- Creating Sequence
-- ==================
CREATE SEQUENCE invoices_id_seq
START WITH 1
INCREMENT BY 1
MINVALUE 1
MAXVALUE 999999
NOCYCLE;

-- Creating Trigger
-- =================
CREATE OR REPLACE TRIGGER trg_invoices_id
  BEFORE INSERT ON invoices
  FOR EACH ROW
BEGIN
  SELECT invoices_id_seq.NEXTVAL
    INTO :NEW.id
    FROM dual;
END;

-- Inserting Without Primary Key
-- ==============================
INSERT INTO invoices(inv_name) VALUES ('INV/02042019/000001');
INSERT INTO invoices(inv_name) VALUES ('INV/02042019/000002');

-- Checking 
-- =========
SELECT * FROM invoices;
SELECT invoices_id_seq.CURRVAL FROM dual;

-- Clearing Table & Dropping Sequence
-- ===================================
TRUNCATE TABLE invoices;
DROP SEQUENCE invoices_id_seq;
DROP TABLE invoices;

Example 4 – Unique, Sequential Code

  • Making “inv_name” auto-generated.
    • Eg: “INV/01042019/000001”
  • Making primary key(id) also auto-generated.
-- Creating Table
-- ===============
CREATE TABLE invoices(
  id NUMBER PRIMARY KEY,
  inv_name VARCHAR2(25)
);

-- Creating Sequence
-- ==================
CREATE SEQUENCE invoices_id_seq
START WITH 1
INCREMENT BY 1
MINVALUE 1
MAXVALUE 999999
NOCYCLE;

-- Creating Function
-- ==================
CREATE OR REPLACE FUNCTION next_inv_name_fn 
  RETURN VARCHAR2
  AS
  BEGIN
  RETURN('INV/' || TO_CHAR(SYSDATE, 'DDMMYYYY') || '/' || LPAD(invoices_id_seq.NEXTVAL, 6, 0));
  END;

-- Creating Trigger
-- =================
CREATE OR REPLACE TRIGGER trg_invoices_id
  BEFORE INSERT ON invoices
  FOR EACH ROW
BEGIN
  SELECT invoices_id_seq.CURRVAL
    INTO :NEW.id
    FROM dual;
END;

-- Inserting Values
-- =================
INSERT INTO invoices(inv_name) VALUES (next_inv_name_fn);
INSERT INTO invoices(inv_name) VALUES (next_inv_name_fn);
INSERT INTO invoices(inv_name) VALUES (next_inv_name_fn);

-- Checking
-- =========
SELECT * FROM invoices;
SELECT invoices_id_seq.CURRVAL FROM dual;

-- Clearing Table & Dropping Sequence
-- ===================================
TRUNCATE TABLE invoices;
DROP SEQUENCE invoices_id_seq;
DROP TABLE invoices;

References

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s