Generally speaking, SEQUENCE objects provide a sequential serie of numbers used for primary and unique keys.
In order to refer to the sequence values in SQL statements, we use CURRVAL and NEXTVAL pseudocolumns to respectively return the sequence's current value and increment it. Examples always make things clearer!
SELECT deptid.CURRVAL --returns the current deptid value
INTO d_deptid
FROM DUAL;
SELECT deptid.NEXTVAL --returns the next deptid value
INTO d_deptid
FROM DUAL;
However, both CURRVAL and NEXTVAL couldn't be used within a PL/SQL expression with Oracle's previous releases.
Oracle was aware of that problem. Therefore, Oracle Database 11g now introduces CURRVAL and NEXTVAL support within PL/SQL code.
Thus, simply proceed like this:
d_deptid := deptid.CURRVAL; --returns the current deptid value
d_deptid := deptid.NEXTVAL; --returns the following deptid value
Source:
Quest-Pipelines.com
