Sequence

A sequence is a database object that generates a series of integers. We create a sequence with the keyword CREATE SEQUENCE

Syntax:

CREATE SEQUENCE <sequence_name>

[START WITH <start_value>]
[INCREMENT BY <increment_value>]
[MINVALUE <min_value> | NOMINVALUE]
[MAXVALUE <max_value> | NOMAXVALUE]
[CYCLE | NOCYCLE]
[ORDER | NOORDER];




Ex:
Create a sequence




CREATE SEQUENCE myseq
 START WITH 1
 INCREMENT BY 5
 MINVALUE 0
 MAXVALUE 100
 CYCLE




Ex:
CURRVAL and NEXTVAL

SELECT myseq.CURRVAL
   FROM dual

*
Error at line 0
ORA-08002: sequence MYSEQ.CURRVAL is not yet defined in this session




Ex:

SELECT myseq.NEXTVAL
  FROM dual

   NEXTVAL
----------
         1
1 row selected.




Ex: 
CREATE SEQUENCE myseq
 START WITH 3
 INCREMENT BY 2
 MINVALUE 0
 MAXVALUE 40
 CYCLE



SELECT myseq.CURRVAL
  FROM dual

*
Error at line 0
ORA-08002: sequence MYSEQ.CURRVAL is not yet defined in this session

The above Sequence will generate the below series

3, 5, 7. . . . 39, 0, 2, 4 . . . . 40, 0, 2 . . . .




Ex:
Without CYCLE Clause




CREATE SEQUENCE myseq
 START WITH 3
 INCREMENT BY 2
 MINVALUE 0
 MAXVALUE 40

The above Sequence will generate the below series

3, 5, 7. . . . 39
After this it throws the below error as the sequence reached its Max Value.


SELECT myseq.NEXTVAL
   FROM dual
*
Error at line 0
ORA-08004: sequence MYSEQ.NEXTVAL exceeds MAXVALUE and cannot be instantiated




Ex: 
Dropping a view

DROP SEQUENCE myseq;