Create Nested Cursor - PL/SQL Program
Consider the following tables to complete the following assignment.
Table 1: 'Emp_Detail'
| Employee_id | First_Name | Last_name | Salary | DEPT_ID |
|---|
| 1 | Shruti | Shrabya | 50000 | 1 |
| 2 | Jaya | Singh | 10000 | 2 |
| 3 | Mangala | Thokal | 60000 | 3 |
| 4 | Surendra | Maurya | 70000 | 4 |
Table2: 'Department'
| Dept_ID | Dept_Name | Manager_ID |
|---|
| 1 | Accounting | 1 |
| 2 | Shipping | 3 |
| 3 | Store | 3 |
Q. Write a PL/SQL block to create nested cursor using cursors for loop and display the records of employees department wise.
Answer:
DECLARE
v_dept_id department.dept_id%TYPE;
CURSOR c_dept IS
SELECT *
FROM department
WHERE manager_id IS NOT NULL
ORDER BY dept_name;
CURSOR c_emp IS
SELECT *
FROM emp_detail
WHERE dept_id = v_dept_id;
BEGIN
FOR r_dept IN c_dept
LOOP
v_dept_id := r_dept.dept_id;
DBMS_OUTPUT.PUT_LINE('----------------------------------');
DBMS_OUTPUT.PUT_LINE('Department Name : '||r_dept.dept_name);
DBMS_OUTPUT.PUT_LINE('----------------------------------');
FOR r_emp IN c_emp
LOOP
DBMS_OUTPUT.PUT_LINE('Employee Name : '||r_emp.first_name);
END LOOP;
END LOOP;
END;
This code will display the records of employees department wise for selected columns.
Output:
