Oracle PL/SQL - CURRVAL and NEXTVAL Pseudo columns

Introduction

The following code generates a sequence number for the sequence emp_SEQ and refers to that number in multiple statements.

Demo

SQL>
SQL> drop SEQUENCE emp_seq;
SQL>--   w  w w  . jav a  2s .  c o m
SQL> CREATE SEQUENCE emp_seq
  2     START WITH 207
  3     INCREMENT BY 1
  4     NOCACHE
  5     NOCYCLE;

Sequence created.

SQL>
SQL> drop table emp;

Table dropped.

SQL> CREATE TABLE emp(
  2  empid NUMBER(6),
  3  first_name VARCHAR2(20),
  4  last_name VARCHAR2(25),
  5  email VARCHAR2(25),
  6  phone_number VARCHAR2(20),
  7  hire_date DATE,
  8  job_id VARCHAR2(10),
  9  salary NUMBER(8,2),
 10  commission_pct NUMBER(2,2),
 11  manager_id NUMBER(6),
 12  department_id NUMBER(4)) ;
SQL>
SQL> INSERT INTO emp VALUES( 100, 'Steven', 'King', 'SKING', '123.123.4567', TO_DATE('17-JUN-1987', 'dd-MON-yyyy'), 'CODER', 24000, NULL, NULL, 90);
SQL>
SQL> DROP TABLE emp_temp;

Table dropped.

SQL> CREATE TABLE emp_temp AS
  2    SELECT empid, first_name, last_name
  3    FROM emp;
SQL>
SQL> CREATE TABLE emp_temp2 AS
  2    SELECT empid, first_name, last_name
  3    FROM emp;
SQL>
SQL> DECLARE
  2    seq_value NUMBER;
  3  BEGIN
  4  
  5
  6    seq_value := emp_seq.NEXTVAL;
  7
  8   
  9
 10    DBMS_OUTPUT.PUT_LINE (
 11      'Initial sequence value: ' || TO_CHAR(seq_value)
 12    );
 13
 14   
 15
 16       INSERT INTO emp_temp (empid, first_name, last_name)
 17       VALUES (emp_seq.NEXTVAL, 'Lynette', 'Smith');
 18
 19   
 20
 21       INSERT INTO emp_temp2 VALUES (emp_seq.CURRVAL,
 22       'Morgan', 'Smith');
 23
 24   
 25
 26       seq_value := emp_seq.CURRVAL;
 27
 28       DELETE FROM emp_temp2
 29       WHERE empid = seq_value;
 30
 31   
 32
 33       UPDATE emp_temp
 34       SET empid = emp_seq.NEXTVAL
 35       WHERE first_name = 'Lynette'
 36       AND last_name = 'Smith';
 37
 38    
 39
 40       seq_value := emp_seq.CURRVAL;
 41
 42       DBMS_OUTPUT.PUT_LINE('Ending sequence value: ' || TO_CHAR(seq_value));
 43  END;
 44  /
Initial sequence value: 207
Ending sequence value: 209

PL/SQL procedure successfully completed.

SQL>