| ☰ See All Chapters |
Building Sequences in Oracle
In Oracle, we can create an auto numbered 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 to create sequence in oracle
CREATE SEQUENCE sequence_name MINVALUE value MAXVALUE value START WITH value INCREMENT BY value CACHE value; |
Sequence example in oracle
CREATE SEQUENCE supplier_seq MINVALUE 1 MAXVALUE 999999999999999999999999999 START WITH 1 INCREMENT BY 1 CACHE 20; |
This would create a sequence object called supplier_seq. The first sequence number that it would use is 1 and each subsequent number would increment by 1 (ie: 2,3,4,...}. It will cache up to 20 values for performance.
If you omit the MAXVALUE option, your sequence will automatically default to:
MAXVALUE 999999999999999999999999999
So you can simplify your CREATE SEQUENCE command as follows:
CREATE SEQUENCE supplier_seq MINVALUE 1 START WITH 1 INCREMENT BY 1 CACHE 20; |
To retrieve the next value in the sequence order, you need to use nextval. For example:
supplier_seq.nextval
This would retrieve the next value from supplier_seq. The nextval statement needs to be used in a SQL statement. For example:
CREATE SEQUENCE supplier_seq MINVALUE 1 START WITH 1 INCREMENT BY 1 CACHE 20;
CREATE TABLE employees( emp_no INT PRIMARY KEY, first_name VARCHAR(50), last_name VARCHAR(50) );
INSERT INTO employees(emp_no,first_name,last_name) VALUES (supplier_seq.nextval,'Manu','M') ; INSERT INTO employees(emp_no,first_name,last_name) VALUES(supplier_seq.nextval,'Jack','Lee'); INSERT INTO employees(emp_no,first_name,last_name) VALUES(supplier_seq.nextval,'Adi','Temp'); COMMIT; |
DROP SEQUENCE
The syntax to a drop a sequence in Oracle is:
DROP SEQUENCE sequence_name; |
sequence_name is the name of the sequence that you wish to drop.
Example to drop sequence
DROP SEQUENCE supplier_seq; |
All Chapters
