Sequences

A sequence:

• Can automatically generate unique numbers
• Is a sharable object
• Can be used to create a primary key value
• Replaces application code
• Speeds up the efficiency of accessing sequence values when cached in memory

CREATE SEQUENCE Statement: 

• Define a sequence to generate sequential numbers automatically
• Syntax:

        CREATE [ TEMPORARY | TEMP ] SEQUENCE name         
        [ INCREMENT [ BY ] increment ]         
        [ MINVALUE minvalue | NO MINVALUE ]         
        [ MAXVALUE maxvalue | NO MAXVALUE ]        
        [ START [ WITH ] start ]        
        [ CACHE cache ] [ [ NO ] CYCLE ]         
        [ OWNED BY { table.column | NONE } ]

Creating a Sequence

• Create a sequence named DEPT_DEPTID_SEQ to be used for the primary key of the DEPARTMENTS table. 
• Do not use the CYCLE option.

        CREATE SEQUENCE dept_deptid_seq 
                INCREMENT BY 10
                START WITH 120
                MAXVALUE 9999
                NO CYCLE;

NEXTVAL and CURRVAL Pseudocolumns

• NEXTVAL returns the next available sequence value. It returns a unique value every time it is referenced, even for different users.
• CURRVAL obtains the current sequence value.
• NEXTVAL must be issued for that sequence before CURRVAL contains a value.

Using a Sequence

• Insert a new department named “Support” in location Boston:

        INSERT INTO departments 
                       (department_id, department_name, location_id)
        VALUES (NEXTVAL(‘dept_deptid_seq’), 'Support', ‘Boston’);

• View the current value for the DEPT_DEPTID_SEQ sequence:

        SELECT CURRVAL(‘dept_deptid_seq’);

Caching Sequence Values

• Caching sequence values in memory gives faster access to those values.
• Gaps in sequence values can occur when:
• A rollback occurs
• The system crashes
• A sequence is used in another table

Modifying a Sequence

• Change the increment value, maximum value, minimum value, cycle option, or cache option:

        ALTER SEQUENCE dept_deptid_seq
                    INCREMENT BY 20
                    MAXVALUE 999999
                   NO CYCLE;