CURRVAL and NEXTVAL
The series is a schema object which generates the sequential numbers. Whenever you form a sequence, you can specify its primary value and an increment. The CURRVAL returns the present value in a specified sequence.
Before you can reference a CURRVAL in a session, you should use NEXTVAL to generate a number. The reference to NEXTVAL stores the present sequence number in the CURRVAL.
The NEXTVAL increments the series and returns the next value. To obtain the present or next value in a series, you should use dot notation, as shown:
sequence_name.CURRVAL
sequence_name.NEXTVAL
After creating a sequence, you can use it to generate exclusive series numbers for the transaction processing. Though, you can use the CURRVAL and NEXTVAL only in a SELECT list, the VALUES clause, & the SET clause. In the illustration below, you use a series to insert the similar employee number into two tables:
INSERT INTO emp VALUES (empno_seq.NEXTVAL, my_ename, ...);
INSERT INTO sals VALUES (empno_seq.CURRVAL, my_sal, ...);
When a transaction generates a sequence number, the series is incremented directly whether you commit or roll back the transaction.