Home » SQL » 5 Oracle Create Sequence Examples

5 Oracle Create Sequence Examples

By

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

  1. 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.

  1. 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.

  1. 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

  1. 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.

  1. 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.

See also: