Summary -

In this topic, we described about the SEQUENCE & NEXTVAL with detailed example.

SEQUENCE & NEXTVAL statements are used to insert values automatically PRIMAR and UNIQUE columns while adding new rows on table. SEQUENCE statement is used to generate UNIQUE values on particular column in existing table with starting value and increment by value. NEXTVAL statement is used to insert values on existing table by increasing old sequence value with increment by value and returns generated new value. SEQUENCE& NEXTVAL statements are used in ORACLE database.

Where we can use SEQUENCE values –

  • In VALUES clause of INSERT statement while inserting values in table
  • In SELECT list of SELECT statement that is not contained a subquery or a view.
  • In SELECT list of subquery INSERT statement.
  • In SET clause of UPDATE statement.

Where we cannot use SEQUENCE values –

  • In DELETE, SELECT and UPDATE statements of a subquery.
  • In the query of view.
  • Along with DISTINCT operator in SELECT statement.
  • In GROUP BY and ORDER BY clauses in SELECT statement.
  • In SELECT statement when combined with another SELECT statement by using UNION, INTERSECT, and MINUS operators.
  • In SELECT statement which consists WHERE clause.
  • In SELECT, UPDATE, DELETE statements which consists of CHECK constraint.
  • Either CREATE TABLE or ALTER TABLE statements when contain DEFAULT value.

Syntax for creating SEQUENCE in ORACLE –

CREATE SEQUENCE sequence_name 
START WITH (initial_value)
INCREMENT BY (interval_value);  

Syntax for inserting rows with NEXTVAL in ORACLE –

INSERT INTO table_name VALUES (sequence_name, value1, value2); 

Syntax for changing SEQUENCE value in ORACLE -

ALTER SEQUENCE sequence_name
INCREMENT BY value; 

Syntax for drop existing SEQUENCE in ORACLE –

DROP SEQUENCE sequence_name; 

Let us consider below table(s) as an example table(s) to frame the SQL query for getting the desired results.

employee_details -

emp_id INT
emp_name VARCHAR (20)
designation VARCHAR (20)
manager_id VARCHAR (20)
date_of_hire DATE
salary INT
dept_id INT

Scenario – Creating Sequence on existing table.

Requirement – Creating SEQUENCE on employee_details table. The query was as follows –

CREATE SEQUENCE seq_employee_details START WITH 1 INCREMENT BY 2;

By executing above query we can create sequence on employee_details table.


Scenario – Inserting rows in existing table by using NEXTVAL statement.

Requirement - Inserting rows in employee_details table by using NEXTVAL statement. The query was as follows –

INSERT INTO employee_details(seq_employee_details,' Employee1',
'Director', '2019-07-11', 45000, 1000); 

INSERT INTO employee_details(seq_employee_details,'Employee2',
'Director','2019-07-11', 40000, 2000); 

By executing above query we can get emp_id by default value 1for Employee1 and the. The output was as follows -

employee_details -

emp_id emp_name designation manager_id date_of_hire salary dept_id
001 Employee1 Director 2019-07-11 45000.00 1000
003 Employee2 Director 2019-07-11 40000.00 2000

Scenario – Finding next value from the SEQUENCE.

Requirement – Finding next value in the SEQUENCE from seq_employee_details. The query was as follows –

SELECT seq_employee_details.NEXTVAL FROM DUAL; 

By executing above query we can get next sequence value from seq_employee_details. The output was as follows –

NEXTVAL
005

Scenario – Changing SEQUENCE value by using ALTER SEQUENCE statement.

Requirement – Changing SEQUENCE value on seq_employee_details by using ALTER SEQUENCE statement. The query was as follows –

ALTER SEQUENCE seq_employee_details 
INCREMENT BY 100; 

By executing above query we can change the sequence value to 100.To know that 100 value is the next sequence value by using below query -

SELECT  seq_employee_details.NEXTVAL  FROM  DUAL;

NEXTVAL
100

Scenario - Deleting existing SEQUENCE in ORACLE .

Requirement - Deleting seq_employee_details sequence in ORACLE.The query was follows

DROP SEQUENCE seq_employee_details;

By executing above query we can drop existing sequence.