Increase Salary

/*
Ermittle das kleinste Durchschnittsgehalt der Abteilung. Führe danach bei jedem Mitarbeiter eine Erhöhung um 3% aus der unter dem Durchschnittsgehalt liegt
*/
SET serveroutput ON;
DECLARE
dgehalt emp.sal%TYPE;
TYPE dept_type_record IS record (
deptno dept.deptno%TYPE,
deptsal emp.sal%TYPE := 0);
TYPE dept_type IS TABLE OF dept_type_record;
TYPE emp_type IS TABLE OF emp%rowtype;
anz_dept INTEGER;
anz_emp INTEGER;
dept_t dept_type;
emp_t emp_type;
emp_anz INTEGER;

BEGIN
SELECT deptno, NULL bulk collect INTO dept_t FROM dept;

FOR i IN 1..dept_t.COUNT loop
  SELECT SUM(sal) INTO dept_t(i).deptsal FROM emp WHERE deptno = dept_t(i).deptno;
  SELECT COUNT (*) INTO emp_anz FROM emp WHERE deptno = dept_t(i).deptno;
  dept_t(i).deptsal := dept_t(i).deptsal / emp_anz;

  SELECT * bulk collect INTO emp_t FROM emp WHERE deptno = dept_t (i).deptno;
  FOR t IN 1..emp_t.COUNT loop
    IF (emp_t(t).sal < dept_t(i).deptsal) THEN
      UPDATE emp SET sal = emp_t(t).sal*1.03 WHERE empno = emp_t(t).empno;
    END IF;
  END loop;

  dbms_output.put_line ('DEPT: ' || dept_t(i).deptno || ' D_Gehalt: ' || dept_t(i).deptsal);
END loop;
END;