Sequence in Oracle

What is a Sequence?

• A sequence is a user created database object that can be shared by multiple users to generate unique integers.
• A typical usage for sequences is to create a primary key value, which must be unique for each row.
• The sequence is generated and incremented (or decremented) by an internal Oracle routine.
• This can be a time-saving object because it can reduce the amount of application code needed to write a sequence-generating routine.
• Sequence numbers are stored and generated independently of tables. Therefore, the same sequence can be used for multiple tables.

You create a sequence using the CREATE SEQUENCE statement, which has the following syntax:

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

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

where

1. The default start_num is 1.
2. The default increment number is 1.
3. The absolute value of increment_num must be less than the difference between maximum_num and minimum_num.
4. minimum_num must be less than or equal to start_num, and minimum_num must be less than maximum_num.
5. NOMINVALUE specifies the maximum is 1 for an ascending sequence or -10^26 for a descending sequence.
6. NOMINVALUE is the default.
7. maximum_num must be greater than or equal to start_num, and maximum_num must be greater than minimum_num.
8. NOMAXVALUE specifies the maximum is 10^27 for an ascending sequence or C1 for a descending sequence.
9. NOMAXVALUE is the default.
10. CYCLE specifies the sequence generates integers even after reaching its maximum or minimum value.
11. When an ascending sequence reaches its maximum value, the next value generated is the minimum.
12. When a descending sequence reaches its minimum value, the next value generated is the maximum.
13. NOCYCLE specifies the sequence cannot generate any more integers after reaching its maximum or minimum value.
14. NOCYCLE is the default.
15. CACHE cache_num specifies the number of integers to keep in memory.
16. The default number of integers to cache is 20.
17. The minimum number of integers that may be cached is 2.
18. The maximum integers that may be cached is determined by the formula CEIL(maximum_num – minimum_num)/ABS(increment_num).
19. NOCACHE specifies no integers are to be stored.
20. ORDER guarantees the integers are generated in the order of the request.
21. You typically use ORDER when using Real Application Clusters.
22. NOORDER doesn’t guarantee the integers are generated in the order of the request.
23. NOORDER is the default

Creating a sequence and then get the next value

CREATE SEQUENCE test_seq; Sequence created. SELECT test_seq.nextval FROM DUAL; NEXTVAL ---------- 1 INSERT INTO test VALUES (test_seq.nextval,'Record A'); ---Using a sequence to populate a table's column

CREATE SEQUENCE test_seq;   Sequence created.   SELECT test_seq.nextval FROM DUAL;   NEXTVAL ----------     1   INSERT INTO test VALUES (test_seq.nextval,'Record A');                   ---Using a sequence to populate a table's column

Once initialized, you can get the current value from the sequence using currval.

SELECT test_seq.nextval FROM dual; NEXTVAL ---------- 1 SELECT test_seq.currval FROM dual; CURRVAL ---------- 1

SELECT test_seq.nextval FROM dual;   NEXTVAL ----------      1 SELECT test_seq.currval FROM dual;   CURRVAL ----------      1

You can’t use CURRVAL just after a sequence creation. It will throw an error.

create sequence deptno_seq start with 50 increment by 10; Sequence created. select deptno_seq.currval from dual; select deptno_seq.currval * ERROR at line 1: ORA-08002: sequence DEPTNO_SEQ.CURRVAL is not yet defined in this session select deptno_seq.currval, deptno_seq.nextval from dual; CURRVAL NEXTVAL ---------- ---------- 50 50 ----Will Work

create sequence deptno_seq start with 50 increment by 10;   Sequence created.   select deptno_seq.currval from dual;   select deptno_seq.currval        * ERROR at line 1: ORA-08002: sequence DEPTNO_SEQ.CURRVAL is not yet defined in this session   select deptno_seq.currval, deptno_seq.nextval from dual;   CURRVAL    NEXTVAL ---------- ----------      50         50               ----Will Work

When you select currval , nextval remains unchanged; nextval only changes when you select nextval to get the next value.

SELECT test_seq.nextval FROM dual; NEXTVAL ---------- 1 SELECT test_seq.nextval, test_seq.currval FROM dual; NEXTVAL CURRVAL --------- --------- 2 2

SELECT test_seq.nextval FROM dual;   NEXTVAL ----------      1 SELECT test_seq.nextval, test_seq.currval FROM dual;   NEXTVAL    CURRVAL ---------   ---------      2          2

Getting Information on Sequences
You get information on your sequences from user_sequences.

desc user_sequences; Name Null? Type ------------------------------------------------------------------- SEQUENCE_NAME NOT NULL VARCHAR2(30) MIN_VALUE NUMBER MAX_VALUE NUMBER INCREMENT_BY NOT NULL NUMBER CYCLE_FLAG VARCHAR2(1) ORDER_FLAG VARCHAR2(1) CACHE_SIZE NOT NULL NUMBER LAST_NUMBER NOT NULL NUMBER select * from user_sequences;

desc user_sequences;   Name                    Null?             Type ------------------------------------------------------------------- SEQUENCE_NAME     NOT NULL               VARCHAR2(30) MIN_VALUE                                NUMBER MAX_VALUE                                NUMBER INCREMENT_BY        NOT NULL            NUMBER CYCLE_FLAG                               VARCHAR2(1) ORDER_FLAG                               VARCHAR2(1) CACHE_SIZE           NOT NULL              NUMBER LAST_NUMBER         NOT NULL             NUMBER   select * from user_sequences;

Modifying a Sequence

• You modify a sequence using the ALTER SEQUENCE statement.
• You cannot change the start value of a sequence.
• The minimum value cannot be more than the current value of the sequence ( currval ).
• The maximum value cannot be less than the current value of the sequence ( currval ).

Removing a Sequence
• Remove a sequence from the data dictionary by using the DROP SEQUENCE statement.
• Once removed, the sequence can no longer be referenced.

DROP SEQUENCE dept_deptid_seq; Sequence dropped.

DROP SEQUENCE dept_deptid_seq;   Sequence dropped.