Example of Oracle Sequence

Topic Introduction: Example of Oracle Sequence

Structure:

CREATE SEQUENCE schema_name.sequence_name

[INCREMENT BY interval]

[START WITH first_number]

[MAXVALUE max_value | NOMAXVALUE]

[MINVALUE min_value | NOMINVALUE]

[CYCLE | NOCYCLE]

[CACHE cache_size | NOCACHE]

[ORDER | NOORDER];

Create Sequence

CREATE SEQUENCE seq_empidpk INCREMENT BY 1

                            START WITH 1

                            MINVALUE 1

                            MAXVALUE 999999999

                            CYCLE

                            CACHE 20;

Check Current Value

SELECT seq_empidpk.CURRVAL FROM DUAL;

Check Next Value

SELECT seq_empidpk.NEXTVAL FROM DUAL;

Check Next Ten Value

    SELECT seq_empidpk.NEXTVAL

      FROM DUAL

CONNECT BY LEVEL <= 10;

Create TriggerUsing Sequence

CREATE OR REPLACE TRIGGER trig_empidpk

    BEFORE INSERT

    ON employees

    FOR EACH ROW

BEGIN

    IF :new.employee_id IS NULL

    THEN

        SELECT seq_empidpk.NEXTVAL INTO :new.employee_id FROM DUAL;

    --:new.employee_id := seq_empidpk.NEXTVAL;

    END IF;

END;