What is Sequences in Oracle? How to use it?

Sequence:-
            Sequence is a database object, it use to generate a numeric value only.


Syntax:-
           
    CREATE SEQUENCE sequence_name
    [INCREAMENT BY n]
    [{START WITH n}]
    [{MAXVALUE n | NOMAXVALUE}]
    [{MINVALUE n | NOMINVALUE}]
    [{NOCYCLE | CYCLE}]
    [{NOCACHE | CACHE n}]
   
   
   
INCREAMENT BY n :- Specifies the interval between sequence of number(n is interger).By default in sequence number increment by 1.

START WITH n :- Specific the first sequence of number.By default in sequence number start with 1.

MAXVALUE n :- Specifies the maximum value sequence can be generate.

NOMAXVALUE :- Specifies a maximum value is 10^27.

MINVALUE n :- Specific the minimum value.

NOMINVALUE :- Specifies a minimum value is -10^26.

CYCLE :- Specifies whether the sequence continue to generate value after reaching maximum or minimum.

NOCYCLE :- After reaching maximum or minimum, sequence may not generate value and it give error.
 NOCYCLE is default option.

CACHE n | NOCACHE :- Specifies how many value the oracle preallocates and keeps in memory.(BY default it is 20).

NEXTVAL:- It returns the next value of the sequence.

CURRVAL:- It returns the current value of the sequence.


Example:-

    CREATE SEQUENCE XXC05_SEQ
    START WITH 1
    INCREMENT BY 1
    MAXVALUE 100
    MINVALUE 1
    NOCYCLE
    NOCACHE;

Modify Sequence:- We can modify sequence.

Syntax:-

    ALTER SEQUENCE sequence_name
    INCREAMENT BY n
    MAXVALUE n
    MINVALUE n
    CYCLE | NOCYCLE
    CACHE | NOCACHE;
   
Note:- we can not modify starting value of sequence. Maximum value cannot less current value.

Example:-

    ALTER SEQUENCE XXC05_SEQ
    INCREAMENT BY 10
    MAXVALUE 1000
    MINVALUE -1000
    CYCLE
    CACHE;

Drop Sequence:- We can drop a sequence.

Syntax:-

DROP SEQUENCE sequence_name;

Example:-

DROP SEQUENCE XXC05_SEQ;