Create cursor in nested loops & display records

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 a cursor in nested loops and display the records department wise.

Answer:

DECLARE
CURSOR c_dept IS
  SELECT *
  FROM department
  WHERE manager_id IS NOT NULL
  ORDER BY dept_name;
  r_dept c_dept%ROWTYPE;

--Declaration of department cursor and record variable.
  
  CURSOR c_emp (c_dept_no department.dept_id%TYPE) IS
  SELECT *
  FROM emp_detail
  WHERE dept_id = c_dept_no;
  r_emp c_emp%ROWTYPE;

--Declaration of employees cursor and record variable.
  
BEGIN
   OPEN c_dept;
   LOOP
     FETCH c_dept INTO r_dept;
      EXIT WHEN c_dept%NOTFOUND;
      DBMS_OUTPUT.PUT_LINE('----------------------------------');
      DBMS_OUTPUT.PUT_LINE('Department Name : '||r_dept.dept_name);
      DBMS_OUTPUT.PUT_LINE('----------------------------------');
          OPEN c_emp(r_dept.dept_id);
           LOOP
            FETCH c_emp INTO r_emp;
             EXIT WHEN c_emp%NOTFOUND;
                DBMS_OUTPUT.PUT_LINE('Employees Details : '||r_emp.employee_id
||' '||r_emp.first_name||' '||r_emp.last_name||' '||r_emp.salary);
           END LOOP;
          CLOSE c_emp;  
   END LOOP;
   CLOSE c_dept;
END;


The above code will display the records of employees for selected columns as department wise.

Output:

cursor in nested loop