Sequences (Part 1)

(Tested on an Oracle 10 database.)

A sequence creates a list of unique numbers which can be added to one or more tables. You might use one to generate employee numbers for a personnel application. The same sequence can be used simultaneously by one, two or several users.

The example below creates a table, a sequence and trigger. Each time a row is added to the table, the trigger gets the next value from the sequence and puts it in the andrews_counter column:

SQL> create table andrews_table
  2  (andrews_counter number,
  3   andrews_name varchar2(10))
  4  /

Table created.

SQL> create sequence andrews_sequence
  2  start with 1
  3  increment by 1
  4  nomaxvalue
  5  /

Sequence created.

SQL> create trigger andrews_trigger
  2  before insert on andrews_table
  3  for each row
  4  begin
  5  select andrews_sequence.nextval
  6  into :new.andrews_counter
  7  from dual;
  8  end;
  9  /

Trigger created.

SQL> insert into andrews_table
  2  values (null, 'Alan')
  3  /

1 row created.

SQL> insert into andrews_table
  2  values (null, 'Brian')
  3  /

1 row created.

SQL> insert into andrews_table
  2  values (null, 'Clive')
  3  /

1 row created.

SQL> insert into andrews_table
  2  values (null, 'David')
  3  /

1 row created.

SQL> select *
  2  from andrews_table
  3  /

ANDREWS_COUNTER ANDREWS_NAME
--------------- ------------
              1 Alan
              2 Brian
              3 Clive
              4 David

SQL>

Go to part 2