DECLARE
CURSOR department_cur IS
SELECT department_id,
department_name,
Max(salary) maxsalary
FROM employees
join departments USING (department_id)
GROUP BY department_id,
department_name;
emp_first_name employees.first_name%TYPE;
BEGIN
dbms_output.Put_line('------------------------------------------------------------');
dbms_output.Put_line(Rpad('Name of the Department', 35)
||Rpad('First Name', 25));
dbms_output.Put_line('------------------------------------------------------------');
FOR emp_dept_cur IN department_cur LOOP
BEGIN
SELECT first_name
INTO emp_first_name
FROM employees
WHERE department_id = emp_dept_cur.department_id
AND salary = emp_dept_cur.maxsalary;
dbms_output.Put_line(Rpad(emp_dept_cur.department_name, 35)
|| Rpad(emp_first_name, 25));
EXCEPTION
WHEN too_many_rows THEN
dbms_output.Put_line(Rpad(emp_dept_cur.department_name, 35)
|| ' - More than one employee');
END;
END LOOP;
END;
/
Sample Output:
SQL> /
------------------------------------------------
Name of the Department First Name
------------------------------------------------
Finance Nancy
Shipping Adam
Public Relations Hermann
Purchasing Den
Executive Steven
Administration Jennifer
Accounting Shelley
Human Resources Susan
Marketing Michael
IT Alexander
Sales John
PL/SQL procedure successfully completed.
Sample Output:
need an explanation for this answer? contact us directly to get an explanation for this answer