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

PL/SQL Cursor: Exercise-23 with Solution

Write a program in PL/SQL to insert data into two tables from one table using an implicit 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

    CURSOR cur_stclerk IS
      SELECT employee_id,
	         department_id,
             first_name,
             last_name
      FROM   employees
      WHERE  job_id = 'ST_CLERK';
BEGIN
FOR z_employeeinfo IN cur_stclerk	
    LOOP
        INSERT INTO emp_temp
                    (employee_id,
                     department_id,
                     job_id)
        VALUES      (z_employeeinfo.employee_id,
                     z_employeeinfo.department_id,
                     'ST_CLERK');

        INSERT INTO emp_detls_temp
                    (employee_id,
                     empname)
        VALUES      (z_employeeinfo.employee_id,
                     z_employeeinfo.first_name
                     || ' '
                     ||z_employeeinfo.last_name);
    END LOOP;
    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:

Improve this sample solution and post your code through Disqus

Previous: Write a program in PL/SQL to insert data into two tables from one table using cursor.
Next: Write a program in PL/SQL to create a cursor displays the name and salary of each employee in the EMPLOYEES table whose salary is less than that specified by a passed-in parameter value.