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
...
Sample Output:
need an explanation for this answer? contact us directly to get an explanation for this answer