w3resource

PL/SQL Cursor Exercises: Insert data into two tables from one table using cursor

PL/SQL Cursor: Exercise-22 with Solution

Write a program in PL/SQL to insert data into two tables from one table using cursor.

Sample Solution:

Table: employees

employee_id		integer
first_name		varchar(25)
last_name		varchar(25)
email			archar(25)
phone_number		varchar(15)
hire_date		date
job_id			varchar(25)
salary			integer
commission_pct		decimal(5,2)
manager_id		integer
department_id		integer

PL/SQL Code:

DROP TABLE emp_temp;
CREATE TABLE emp_temp AS
  SELECT employee_id, department_id,job_id
  FROM employees;
DELETE FROM emp_temp;
COMMIT; 

DROP TABLE emp_detls_temp;
CREATE TABLE emp_detls_temp(
employee_id NUMBER,
empname varchar2(40)); 

 

DECLARE
    z_empid employees.employee_id%TYPE;
	z_depid employees.department_id%TYPE;
    z_firstname employees.first_name%TYPE;
    z_lastname  employees.last_name%TYPE;
	
	
    CURSOR cur_stclerk IS
      SELECT employee_id,
	         department_id,
             first_name,
             last_name
      FROM   employees
      WHERE  job_id = 'ST_CLERK';
BEGIN
    OPEN cur_stclerk; 
    LOOP
        FETCH cur_stclerk INTO z_empid,z_depid,z_firstname, z_lastname;
        EXIT WHEN cur_stclerk%NOTFOUND;
        INSERT INTO emp_temp
                    (employee_id,
                     department_id,
                     job_id)
        VALUES      (z_empid,
                     z_depid,
                     'ST_CLERK');

        INSERT INTO emp_detls_temp
                    (employee_id,
                     empname)
        VALUES      (z_empid,
                     z_firstname
                     || ' '
                     || z_lastname);
    END LOOP;
    CLOSE cur_stclerk;
    COMMIT; 
END; 
/

Sample Output:

PL/SQL procedure successfully completed.

If you want to see the inserted data from the table emp_temp and emp_detls_temp type the following statement:

select * from emp_temp;
select * from emp_detls_temp;

Flowchart:

Flowchart: PL/SQL Cursor Exercises - Insert data into two tables from one table using cursor

Improve this sample solution and post your code through Disqus

Previous: Write a program in PL/SQL to print a list of managers and the name of the departments.
Next: Write a program in PL/SQL to insert data into two tables from one table using an implicit cursor.

What is the difficulty level of this exercise?



Become a Patron!

Follow us on Facebook and Twitter for latest update.

It will be nice if you may share this link in any developer community or anywhere else, from where other developers may find this content. Thanks.

https://www.w3resource.com/plsql-exercises/cursor/plsql-cursor-exercise-22.php