PL/SQL Cursor Exercises: Show the uses of CURVAL and NEXTVAL with a sequence name

PL/SQL Cursor: Exercise-2 with Solution

Write a program in PL/SQL to show the uses of CURVAL and NEXTVAL with a sequence name.

You can generate initial sequence number by using the following sql commands.

Sample Solution:

PL/SQL Code:

SQL> CREATE SEQUENCE emp_seq INCREMENT BY 1;

  /* Because NEXTVAL values might be referenced
     by different users and applications,
     and some NEXTVAL values might not be stored in database,
     there might be gaps in sequence. */
  
DROP TABLE emp_temp;
CREATE TABLE emp_temp AS
  SELECT employee_id, first_name, last_name 
  FROM employees;
  
DROP TABLE emp_temp1;
CREATE TABLE emp_temp1 AS
  SELECT employee_id, first_name, last_name 
  FROM employees;  
  
 
DECLARE
  seq_value NUMBER;
BEGIN
  seq_value := emp_seq.NEXTVAL;
   DBMS_OUTPUT.PUT_LINE (
    'Initial sequence value: ' || TO_CHAR(seq_value)
  );
  -- NEXTVAL have been used to create unique number when inserting data:
     INSERT INTO emp_temp (employee_id, first_name, last_name) 
     VALUES (emp_seq.NEXTVAL, 'Alen', 'George');
  -- CURRVAL have been used to store same value somewhere else:
     INSERT INTO emp_temp1 VALUES (emp_seq.CURRVAL,
                                         'Tim', 'May');
  -- CURRVAL have been used to specify record to delete:
     seq_value := emp_seq.CURRVAL;
     DELETE FROM emp_temp
     WHERE employee_id = seq_value;
  -- The employee_id will be updated with NEXTVAL for specified record:
     UPDATE emp_temp
     SET employee_id = emp_seq.NEXTVAL
     WHERE first_name = 'Alen'
     AND last_name = 'George';
     seq_value := emp_seq.CURRVAL;
     DBMS_OUTPUT.PUT_LINE (
       'Ending sequence value: ' || TO_CHAR(seq_value)
     );
END;
/

Sample Output:

SQL> /
Initial sequence value: 23
Ending sequence value: 24

PL/SQL procedure successfully completed.

Flowchart:

Improve this sample solution and post your code through Disqus

Previous: Write a program in PL/SQL to show the uses of static PL/SQL statement.
Next: Write a program in PL/SQL to find the number of rows effected by the use of SQL%ROWCOUNT attributes of an implicit cursor.