About Sequences

Use the CREATE SEQUENCE statement to create a sequence, which is a database object from which multiple users may generate unique integers. You can use sequences to automatically generate primary key values. When a sequence number is generated, the sequence is incremented, independent of the transaction committing or rolling back. If two users concurrently increment the same sequence, then the sequence numbers each user acquires may have gaps, because sequence numbers are being generated by the other user. One user can never acquire the sequence number generated by another user. After a sequence value is generated by one user, that user can continue to access that value regardless of whether the sequence is incremented by another user.

Sequence numbers are generated independently of tables, so the same sequence can be used for one or for multiple tables. It is possible that individual sequence numbers will appear to be skipped, because they were generated and used in a transaction that ultimately rolled back. Additionally, a single user may not realize that other users are drawing from the same sequence.

Creating Sequences

After a sequence is created, you can access its values in SQL statements with the CURRVAL pseudocolumn, which returns the current value of the sequence, or the NEXTVAL pseudocolumn, which increments the sequence and returns the new value.

For example:

create sequence my_sequence start with 1;

Now lets run some code to see how we can use this sequence. The DUAL table is a pseudo table in Oracle you can select from that has one row. Now lets repeat the above, but first lets request the next value from the sequence.

select my_sequence.NEXTVAL
from dual;

select my_sequence.CURRVAL
from dual;

Now repeat the first query

select my_sequence.NEXTVAL
from dual;

Referencing Sequences in Triggers

Perhaps the most common use of sequences is to populate table primary key values from within triggers. The example below creates a table and uses a trigger to populate the primary key.

create sequence simple_employees_seq;

create table SIMPLE_EMPLOYEES (  
  empno             number primary key,  
  name              varchar2(50) not null,  
  job               varchar2(50)
  );

create or replace trigger SIMPLE_EMPLOYEES_BIU_TRIG
    before insert or update on SIMPLE_EMPLOYEES
    for each row
begin
    if inserting and :new.empno is null then
        :new.empno := simple_employees_seq.nextval;
    end if;
end;
/

Testing Triggered Sequence

Lets populate some data and test the trigger based generation of the primary key.

insert into simple_employees (name, job) values ('Mike', 'Programmer');
insert into simple_employees (name, job) values ('Taj', 'Analyst');
insert into simple_employees (name, job) values ('Jill', 'Finance');
insert into simple_employees (name, job) values ('Fred', 'Facilities');
insert into simple_employees (name, job) values ('Sabra', 'Programmer');
commit;

Lets query our table to see how the EMPNO column was populated

select empno, name, job
from simple_employees
order by 1