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

  • pl/sql

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

Nested Cursors Using Cursor FOR Loops

الأجوبة

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
...
هل كان المحتوى مفيد؟

معلومات ذات صلة

تبحث عن مدرس اونلاين؟

محتاج مساعدة باختيار المدرس الافضل؟ تواصل مع فريقنا الان لمساعدتك بتأمين افضل مدرس
ماهو التخصص الذي تبحث عنه؟
اكتب هنا...