In Oracle, Sequences are used to generate numbers in sequential order. You can also specify the increment value, a maximum value and cache or no cache parameters etc at the time of creating a sequence. In this article, I am giving 5 Oracle Create Sequence Examples, which will help you to understand it better.
Oracle Create Sequence Examples
Create Sequence to Start With 1 and Increment by 1
A simple Create Sequence statement to create a sequence for employee number column. The sequence will start generating numbers from 1 and will increment by 1, but not in order because no Order clause specified.
CREATE SEQUENCE empno_seq INCREMENT BY 1;
Test this sequence to get next value.
SELECT empno_seq.NEXTVAL FROM DUAL;
Output:
NEXTVAL ---------- 1 1 row selected.
Create Sequence to Start With 10 and Increment by 20
In the following example, we will create a sequence which will start from 10 and will increment by 20.
CREATE SEQUENCE empno_seq1 start with 10 INCREMENT BY 20;
Now test the sequence by executing it two times. It should start with 10 and for the second time, it should increment by 20.
First execution:
SELECT empno_seq1.NEXTVAL FROM DUAL;
Output:
NEXTVAL ---------- 10 1 row selected.
Second Execution:
SELECT empno_seq1.NEXTVAL FROM DUAL;
Output:
NEXTVAL ---------- 30 1 row selected.
Sequence Having Max Value
Below is the example to create a sequence with MAXVALUE option. This sequence will stop generating the number after reaching number 3.
CREATE SEQUENCE empno_seq2 start with 1 INCREMENT BY 1 maxvalue 3;
Test this sequence:
SELECT empno_seq2.NEXTVAL FROM DUAL;
Output:
NEXTVAL ---------- 1 1 row selected.
If you will try to execute this sequence more than 3 times, it will give you the below error:
* Error at line 0 ORA-08004: sequence EMPNO_SEQ2.NEXTVAL exceeds MAXVALUE and cannot be instantiated
Create Sequence To Generate Numbers in Order
In the following example, we will create a sequence in Oracle which will start with 1 increment by 1 and the numbers will be in order.
CREATE SEQUENCE empno_seq3 start with 1 INCREMENT BY 1 ORDER;
Now, whenever this sequence will generate the number it will always be in order.
Create Sequence to Generate Numbers in Reverse Order
In the following example, we will create a sequence to generate numbers in reverse order. It will start with 1000 and will decrement by -1.
CREATE SEQUENCE empno_seq5 START WITH 1000 INCREMENT BY -1 MAXVALUE 1000;
Get the number from this sequence.
SELECT empno_seq5.NEXTVAL FROM DUAL; SELECT empno_seq5.NEXTVAL FROM DUAL; SELECT empno_seq5.NEXTVAL FROM DUAL;
Output:
NEXTVAL ---------- 1000 1 row selected. NEXTVAL ---------- 999 1 row selected. NEXTVAL ---------- 998 1 row selected.