Live ORACLE

CREATE TABLE employees_temp
 AS
 SELECT employee_id,
        first_name,
        last_name
 FROM   employees;


CREATE TABLE employees_temp2
 AS
 SELECT employee_id,
        first_name,
        last_name
 FROM   employees;



DECLARE

 seq_value  NUMBER;

BEGIN

 -- Display initial value of NEXTVAL

 -- This is invalid: seq_value := employees_seq.NEXTVAL;

 SELECT employees_seq.nextval

 INTO   seq_value

 FROM   dual;



 dbms_output.Put_line('Initial sequence value: '

                      ||To_char(seq_value));



 -- The NEXTVAL value is the same no matter what table you select from

 -- You usually use NEXTVAL to create unique numbers when inserting data.

 INSERT INTO employees_temp

 VALUES     (employees_seq.nextval,

             'Lynette',

             'Smith');



 -- If you need to store the same value somewhere else, you use CURRVAL

 INSERT INTO employees_temp2

 VALUES     (employees_seq.currval,

             'Morgan',

             'Smith');



 -- Because NEXTVAL values might be referenced by different users and

 -- applications, and some NEXTVAL values might not be stored in the

 -- database, there might be gaps in the sequence

 -- The following uses the stored value of the CURRVAL in seq_value to specify

 -- the record to delete because CURRVAL (or NEXTVAL) cannot used in a WHERE clause

 -- This is invalid: WHERE employee_id = employees_seq.CURRVAL;

 SELECT employees_seq.currval

 INTO   seq_value

 FROM   dual;



 DELETE FROM employees_temp2

 WHERE       employee_id = seq_value;



 -- The following udpates the employee_id with NEXTVAL for the specified record

 UPDATE employees_temp

 SET    employee_id = employees_seq.nextval

 WHERE  first_name = 'Lynette'

        AND last_name = 'Smith';



 -- Display end value of CURRVAL

 SELECT employees_seq.currval

 INTO   seq_value

 FROM   dual;



 dbms_output.Put_line('Ending sequence value: '

                      ||To_char(seq_value));

END;

/

Using CURRVAL and NEXTVAL