ORACLE-SEQUENCES

ORACLE-SEQUENCES – Learn how to Generate sequence integers, and Also Learn how to use this sequence integer numbers for Primary key and Unique keys column in tables.

A sequence is a database object that generate sequence integer numbers automatically for primary key column or Unique key column of a table.
You can use sequences to insert unique values in primary key and Unique columns of a table.

Create Sequence Syntax

CREATE SEQUENCE sequence_name
 [START WITH start_num]
 [INCREMENT BY increment_num]
 [MAXVALUE maximum_num | NOMAXVALUE]
 [MINVALUE minimum_num | NOMINVALUE]
 [CACHE cache_num | NOCACHE]
 [CYCLE | NOCYCLE]
 [ORDER | NOORDER];

Example Program to create a sequence

CREATE SEQUENCE SEQ_ID
        START WITH 1
        increment by 1
        minvalue 1
        maxvalue 100
       cycle 
       cache 10;

NEXTVAL and CURRVAL

NEXTVAL column returns the next value of the sequence as well as initializes the sequence.

SELECT SEQ_ID.NEXTVAL FROM DUAL;

Output

NEXTVAL
1

CURRVAL column will return the current value of the sequence.

SELECT SEQ_ID.CURRVAL FROM DUAL;

Output

CURRVAL
1

CREATE SEQUENCE -this are oracle reserved keywords. used to create sequence DDL statement.

sequence_name — its a sequence name, that can be defined based on user’s choice.

START WITH– tells about the starting sequence number from which number to start.

INCREMENT BY — by how many numbers it has to increase to previous number. The value for INCREMENTED BY cannot be 0 but it can be any positive or negative value.

If this value is negative, then the sequence descends. If the value is positive, then the sequence ascends. If you omit this clause, then the interval defaults to 1.

MAXVALUE / NOMAXVALUE
MAXVALUE –sets the maximum value to stop the sequnce number generation.
MAXVALUE must be equal to or greater than START WITH and must be greater than MINVALUE attribute.
If you don’t want to set the MAXVALUE for your sequence then you can use NOMAXVALUE attribute.

MINVALUE / NOMINVALUE
sets the minimum or lowest value to stop the sequnce number generation.
should be less than or equal to START WITH as well as less than MAXVALUE.
If you don’t want to set the lower bound for your sequence then you can use NOMINVALUE attribute instead.

CACHE/ NOCACHE
Cache attribute specify the number of integers to keep in memory.
The default number of integers to cache is 20.
The minimum number of integers that may be cached is 2.

NOCACHE to indicate that values of the sequence are not pre-allocated.
If you omit both CACHE and NOCACHE, the database caches 20 sequence numbers by default.

CYCLE/NOCYCLE

CYCLE and NOCYCLE are two flags to set.
Set the flag on CYCLE, then sequence continues to generate values after reaching either its maximum or minimum value.

NOCYCLE flag when you do not want your sequence to generate more values after reaching its maximum or minimum value.
If omit both these flags, then by default oracle engine will set the flag on NOCYCLE.

ORDER/ NOORDER
ORDER Flag generates sequence numbers in order of request.
This clause is useful if you are using the sequence numbers as timestamps.

Set the flag on NOORDER ,if you do not want to generate the sequence numbers in order of request.
NOORDER is the default flag in case you omit either of them.

Using Sequences in Primary or Unique Columns of a table

Example -to create a table using Primary Key

CREATE TABLE SEQUNCE_TEST_DEMO(DEMO_ID NUMBER, DEMO_NAME VARCHAR2(10));

Example – To insert data into a table using Sequence Values

INSERT INTO SEQUNCE_TEST_DEMO VALUES( SEQ_ID.NEXTVAL,'ABC');

Example query to display the result

SELECT * FROM SEQUNCE_TEST_DEMO ;

Output

DEMO_ID	DEMO_NAME
1	ABC

SELECT SEQ_ID.NEXTVAL FROM DUAL;

Output

NEXTVAL
2

SELECT SEQ_ID.CURRVAL FROM DUAL;

Output

CURRVAL
3

ALTER SEQUENCES

ALTER SEQUENCE SEQ_ID
MAXVALUE 200;

DROP SEQUENCES

Syntax

DROP SEQUENCE sequence_name;

Example- To drop sequence

DROP SEQUENCE SEQ_ID;

ORACLE-SEQUENCES