Create Nested Cursor - PL/SQL Program

Consider the following tables to complete the following assignment.

Table 1:  'Emp_Detail'

Employee_idFirst_NameLast_nameSalaryDEPT_ID
1ShrutiShrabya500001
2JayaSingh100002
3MangalaThokal600003
4SurendraMaurya700004

Table2: 'Department'

Dept_IDDept_NameManager_ID
1Accounting1
2Shipping3
3Store3

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:

nested cursor