Write a program in PL/SQL to FETCH multiple records with the uses of nested cursor
- pl/sql
- 2021-09-28
- mhanasmh00489829403
الأجوبة
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 ...
أسئلة مشابهة
القوائم الدراسية التي ينتمي لها السؤال
معلومات ذات صلة