SEQUENCE in Oracle SQL – PLSQL

In Oracle SQL /PLSQL whenever we want to create an auto-number field we use SEQUENCE. A SEQUENCE is used to generate number sequences in Oracle SQL.
Sequences are very useful whenever we want to create a unique number that can act as a primary key for a table.

Syntax for creation of a SEQUENCE in Oracle SQL / PLSQL is:

CREATE SEQUENCE sequence_name
MINVALUE value
MAXVALUE value
START WITH value
INCREMENT BY value
CACHE value;

Let’s understand each line of the above SQL CREATE SEQUENCE command:

MINVALUE: is the minimum value that the sequence can have.
MAXVALUE: is the maximum value up to which the sequence will go. The maximum value allowed is 999999999999999999999999999. If we omit the MAXVALUE portion from the CREATE SEQUENCE command then the MAXVALUE is defaulted to 999999999999999999999999999
START WITH: is the value where the SEQUENCE starts from. The start value must be greater than or equal to MINVALUE.
INCREMENT BY: is the value by which the current value of the sequence is incremented to get the next value of the sequence.
CACHE: Represents the number of values that the sequence will have in cache for better performance.

Let’s take an example for understanding

Suppose have a table named ‘employee’ in the database as shown below, but we do not have any data in the table.

Let’s create a SEQUENCE and the use it to enter data in ‘employee’ table.

Step 1: Creating a SEQUENCE:

CREATE SEQUENCE employee_seq
MINVALUE 100
MAXVALUE 999999999999999999999999999
START WITH 101
INCREMENT BY 1
CACHE 30;

Here with the help of the above SQL CREATE SEQUENCE command we have created a sequence named ‘employee_seq’ that starts with 101 and can go up to 999999999999999999999999999, it will increment with 1 value at a time and caching up to 30 values for performance.

Step 2: Inserting the data in the ‘employee’ table using the ‘employee_seq’ sequence in ‘employee_id’ field.

INSERT INTO employee
VALUES (employee_seq.nextval,'Emp A',10000,'Sales',10);

INSERT INTO employee
VALUES (employee_seq.nextval,'Emp B',20000,'IT',20);

INSERT INTO employee
VALUES (employee_seq.nextval,'Emp C',28000,'IT',20);

INSERT INTO employee
VALUES (employee_seq.nextval,'Emp D',30000,'Support',NULL);

INSERT INTO employee
VALUES (employee_seq.nextval,'Emp E',32000,'Sales',10);

The above SQL INSERT statement will insert 5 rows in the ‘employee’ table.
Note the in all the above SQL INSERT statements we have used employee_seq.netxval to get the next value for the employee_id field.

Now, if we query the employee table as:

SELECT *
FROM employee;

We will get the following result:

Here we have successfully inserted data in ‘employee’ table using sequence ‘employee_seq’ for the ‘employee_id’ field.