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:
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?
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://w3resource.com/plsql-exercises/cursor/plsql-cursor-exercise-22.php
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics