Write a program in PL/SQL to FETCH multiple records with the uses of nested cursor

  • pl/sql

Write a program in PL/SQL to FETCH multiple records with the uses of nested cursor.

الأجوبة

DECLARE
    e_dept_no employees.department_id%TYPE;
    CURSOR cur_deptartments IS
      SELECT *
      FROM   departments;
    CURSOR cur_employees IS
      SELECT *
      FROM   employees e
      WHERE  e.department_id = e_dept_no;
    v_deptrec departments%ROWTYPE;
    v_emprec  employees%ROWTYPE;
BEGIN
    OPEN cur_deptartments;
    LOOP
        FETCH cur_deptartments INTO v_deptrec;
        exit WHEN cur_deptartments%NOTFOUND;
        e_dept_no := v_deptrec.department_id;
		
		
      DBMS_OUTPUT.PUT_LINE('----------------------------------');
      DBMS_OUTPUT.PUT_LINE('Department Name : '||v_deptrec.department_name);
      DBMS_OUTPUT.PUT_LINE('----------------------------------');		

        OPEN cur_employees;
        LOOP
            FETCH cur_employees INTO v_emprec;
            exit WHEN cur_employees%NOTFOUND;
            dbms_output.Put_line('Employee: '
                                 || v_emprec.first_name
								 ||chr(9)||'Salary: '
								 || v_emprec.salary);
        END LOOP;
        CLOSE cur_employees;

    END LOOP;
    CLOSE cur_deptartments;
END; 
 /

Sample Output:

SQL> /
----------------------------------
Department Name : Administration
----------------------------------
Employee: Jennifer      Salary: 4400
----------------------------------
Department Name : Marketing
----------------------------------
Employee: Michael       Salary: 13000
Employee: Pat   Salary: 6000
----------------------------------
Department Name : Purchasing
----------------------------------
Employee: Den   Salary: 11000
Employee: Alexander     Salary: 3100
Employee: Shelli        Salary: 2900
Employee: Sigal Salary: 2800
Employee: Guy   Salary: 2600
Employee: Karen Salary: 2500
...
هل كان المحتوى مفيد؟

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

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

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