Sequence, Nextval & Currval

Sequence:

A sequence is a database object that generates unique numbers, mostly used for primary key values. One can select the NEXTVAL and CURRVAL from a sequence. Selecting the NEXTVAL will automatically increment the sequence.

Another Definition,

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.

Currval:

CURRVAL can only be used if a session has already called NEXTVAL on a trigger or by sql, CURRVAL will then return the same number that was generated with NEXTVAL.

Create a simple sequence:

CREATE SEQUENCE empseq;

 Sequence created.

Selecting from the sequence:

SQL> select empseq.nextval from dual;

   NEXTVAL

----------

         1

SQL> select empseq.nextval from dual;

   NEXTVAL

----------

         2

Note that nextval and currval returns the same value for each row of a select:

SQL> select empseq.nextval, empseq.currval from dual;

   NEXTVAL    CURRVAL  

   ----------           ----------

3                                            3         

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

/