Q:

Write a program in PL/SQL to FETCH records with nested Cursors using Cursor FOR Loops

0

Write a program in PL/SQL to FETCH records with nested Cursors using Cursor FOR Loops.

Nested Cursors Using Cursor FOR Loops

All Answers

need an explanation for this answer? contact us directly to get an explanation for this answer

DECLARE
  emp_dept_id departments.department_id%TYPE;
 CURSOR cur_dept IS
  SELECT * 
  FROM departments
  WHERE manager_id IS NOT NULL
  ORDER BY department_name;
 CURSOR cur_emp IS
  SELECT * 
  FROM employees
  WHERE department_id = emp_dept_id;
  
BEGIN
    FOR r_dept IN cur_dept
    LOOP
      emp_dept_id := r_dept.department_id;
      DBMS_OUTPUT.PUT_LINE('----------------------------------');
      DBMS_OUTPUT.PUT_LINE('Department Name : '||r_dept.department_name);
      DBMS_OUTPUT.PUT_LINE('----------------------------------');
           FOR r_emp IN cur_emp 
           LOOP
             DBMS_OUTPUT.PUT_LINE('Employee: '||r_emp.last_name);
           END LOOP;   
    END LOOP;
END;
 /

Sample Output:

SQL> /
----------------------------------
Department Name : Accounting
----------------------------------
Employee: Higgins
Employee: Gietz
----------------------------------
Department Name : Administration
----------------------------------
Employee: Whalen
----------------------------------
Department Name : Executive
----------------------------------
Employee: King
Employee: Kochhar
Employee: De Haan
...

need an explanation for this answer? contact us directly to get an explanation for this answer

total answers (1)

Similar questions


need a help?


find thousands of online teachers now