René Nyffenegger's collection of things on the web
René Nyffenegger on Oracle - Most wanted - Feedback -
 

Sequences [Oracle]

A sequence is a highly scalable, non-blocking, generator that generates unique numbers.

nextval

If nextval is invoked on a sequence, it makes sure that a unique number is generated
I create a sequence (named sequence_test) 
create sequence sequence_test;
Then I use the sequence to generate a number. The sequence (or Oracle, for that matter) ensures that no other session or other call to nextval within the same session gets the same number from the sequence: 
select sequence_test.nextval from dual;

   NEXTVAL
----------
         1
I then use nextval again to generate another number: 
select sequence_test.nextval from dual;

   NEXTVAL
----------
         2

currval

currval can only be used if a session has already called nextval on a trigger. currval will then return the same number that was generated with nextval: 
select sequence_test.nextval from dual;

   NEXTVAL
----------
         3

select sequence_test.currval from dual;

   CURRVAL
----------
         3

select sequence_test.currval from dual;

   CURRVAL
----------
         3

select sequence_test.nextval from dual;

   NEXTVAL
----------
         4

select sequence_test.currval from dual;

SQL> select sequence_test.currval from dual;

   CURRVAL
----------
         4

Demonstration

This is demonstrated in the following example. First, two tables are created: 
create table seq_ex_a (
  n number
);

create table seq_ex_b (
  s number,
  n number
);
One table is populated with five rows: 
insert into seq_ex_a values (55);
insert into seq_ex_a values ( 3);
insert into seq_ex_a values (27);
insert into seq_ex_a values (81);
insert into seq_ex_a values (32);
A sequence is generated: 
create sequence seq_a start with 1 increment by 1;
Then, the values of table seq_ex_a are filled into seq_ex_b. The sequence generates a (as mentioned: unique) number by calling nextval on it: 
insert into seq_ex_b select seq_a.nextval, n from seq_ex_a;
The table's content: 
select * from seq_ex_b;
This returns: 
         S          N
---------- ----------
         1         55
         2          3
         3         27
         4         81
         5         32

nextval and currval in PL/SQL

In PL/SQL, up to Oracle 10g, nextval and currval have to be selected from dual: 
declare
  n number;
begin

  select sequence_test.nextval into n from dual;

  dbms_output.put_line(n);

end
/
Apparently, in Oracle 11g, one is not required anymore to select from dual: 
declare
  n number;
begin

  n := sequence_test.nextval;

  dbms_output.put_line(n);

end
/

Links

Sequences, together with triggers can be used to emulate autonumbers.