Creating and Using Sequence in Oracle Database
A sequence is used to auto-generate numbers in ascending or descending order. It can be used to auto-generate a row identifier (primary key) or can be a part of a composite primary key (primary key based on more than one column).
Creating a Sequence
Syntax: CREATE SEQUENCE <SEQUENCE_NAME> [INCREMENT BY <VALUE>] [START WITH <VALUE>] [MAXVALUE N|NOMAXVALUE] [MINVALUE N|NOMINVALUE] [CYCLE|NOCYCLE] [CACHE N|NOCACHE];
Example: CREATE SEQUENCE MYSEQ;
The example shown above is the simplest way to create a sequence. Its initial value will be set to 1 and will be incremented by 1.
Nextval & Currval
CURRVAL and NEXTVAL are two attributes of a sequence used to get the current value of the sequence and to increment the sequence respectively.
Example: MYSEQ.CURRVAL -- Returns the current value MYSEQ.NEXTVAL -- Increments the sequence and returns the next value Note: Once a sequence is created CURRVAL is used to initiate the sequence and set its value to the default value.
Using Increment By, Start With, Maxvalue & Minvalue
Increment By is used to set the increment step.
Start With sets the default value.
Minvalue is used in case of descending sequence to set the minimum value up-to which a sequence can go. In case of ascending sequence min-value is same as start value, and hence either of the two can be used to set the default value.
Maxvalue is similarly used in case of ascending sequence to set the maximum value. Same as Start With in case of descending sequence.
Examples: CREATE SEQUENCE MYSEQ INCREMENT BY 2 START WITH 10 MAXVALUE 30; The sequence would generate values as: 10, 12, 14, 16 ... 30 CREATE SEQUENCE MYSEQ INCREMENT BY -5 START WITH 100 MINVALUE 30; The sequence would generate values as: 100, 95, 90, 85 ... 30
Using Cycle & Cache
Cycle is used to create a cyclic sequence i.e. it would repeat after it has reached the maxvalue.
Cache is used to set the number of times the sequences should be cycled/ repeated.
Example: CREATE SEQUENCE MYSEQ MAXVALUE 10 CYCLE CACHE 3;
The above example will create a sequence which will cycle and repeat from 1 to 10 for 3 times.
Using Sqeuence in a Table
A sequence can be used in an INSERT statement while inserting data into the table as shown below:
Example: INSERT INTO EMP(EMPID, NAME) VALUES(MYSEQ.NEXTVAL, 'CARL');
Each time a record is inserted using the above statement, the EMPID column will be populated with an incremented value.
Modifying a Sequence
We can modify an existing sequence using the ALTER statement. Any property of the sequence can be modified, except START WITH.
Example: ALTER SEQUENCE MYSEQ MAXVALUE 40; ALTER SEQUENCE MYSEQ NOMAXVALUE;
Deleting a Sequence
A sequence can be deleted using DROP statement.
Example: DROP SEQUENCE MYSEQ;