jljl

CREATE OR REPLACE PROCEDURE P3_5_BEST_BETAALDE_PER_DEPT AS 
  CURSOR C_DEPTS IS
    SELECT ID, NAME
    FROM S_DEPT
    ORDER BY ID ASC;
 
  CURSOR C_EMPLOYEE (P_DEPTID IN NUMBER) IS
    SELECT LAST_NAME, FIRST_NAME, MAX(SALARY) SALARY
    FROM S_EMP
    WHERE DEPT_ID = P_DEPTID 
    GROUP BY LAST_NAME, FIRST_NAME;
 
    R_EMP C_EMPLOYEE%ROWTYPE;   
BEGIN
  FOR R_DEPT IN C_DEPTS LOOP
    OPEN C_EMPLOYEE(R_DEPT.ID);
    FETCH C_EMPLOYEE INTO R_EMP;
      DBMS_OUTPUT.PUT_LINE(R_DEPT.ID ||' ' || R_DEPT.NAME || ' ' ||  R_EMP.LAST_NAME || ' ' ||R_EMP.FIRST_NAME || ' ' || R_EMP.SALARY);
    CLOSE C_EMPLOYEE;
  END LOOP;
END P3_5_BEST_BETAALDE_PER_DEPT;