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.