Sequence in Oracle

• Sequence is a database object that is used to generate unique numbers. It can be used for generation of primary key values.

• Sequence numbers are generated independently of tables, so the same sequence can be used for one or multiple tables.

Syntax for creating a sequence:-

create sequence <sequence_name>
  [START WITH <integer>]
  [INCREMENT BY <integer>]
  [MAXVALUE <integer>]
  [MINVALUE <integer>]
  [CACHE <integer> | NO CACHE]
  [CYCLE | NO CYCLE];


Sequence Parameters:-

START WITH:- Specifies the first sequence number to be generated.

INCREMENT BY:- Specifies the interval between sequence numbers.

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

MINVALUE:- Specifies the minimum value of the sequence.

Ex:-

CREATE SEQUENCE xx_seq_id 

 START WITH 1 

 INCREMENT BY 1 

 MAXVALUE 9999;

Once a sequence is created, to retrieve the next value of the sequence use NEXTVAL 

Ex:-

SELECT xx_seq_id.NEXTVAL FROM dual;

To retrieve the current value of the sequence use CURRVAL.

Ex:-
SELECT xx_seq_id.CURRVAL from dual;


Altering a Sequence:-
Syntax:-

ALTER SEQUENCE <sequence_name> <parameter name> <new value>;
Execute the below SQL code to alter the increment value of the sequence xx_seq_id

ALTER SEQUENCE xx_seq_id INCREMENT BY 5;

You cannot alter start value of a sequence.

Deleting a Sequence:-
Syntax:-

DROP SEQUENCE <sequence name>;
Execute the below code to drop the sequence xx_seq_id.

DROP SEQUENCE xx_seq_id.;