Pages

Sunday, November 16, 2014

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
/

No comments:

Post a Comment