 DECLARE
 CURSOR c_emp
 IS
 SELECT deptno,empno,ename,sal
 FROM emp ORDER BY deptno
 FOR UPDATE;
 CURSOR c_avg(p_deptno IN NUMBER)
 IS
 SELECT avg(sal)
 FROM emp
 WHERE deptno = p_deptno;
 v_gemsal emp.sal%type;
 v_oud_sal emp.sal%type;
 v_nieuw_sal emp.sal%type;
 v_deptno emp.deptno%type:=-99;
 per Number(4);
 BEGIN
 FOR r_emp in c_emp LOOP
  IF r_emp.deptno<>v_deptno THEN
   OPEN c_avg(r_emp.deptno);
    FETCH c_avg INTO v_gemsal;
   CLOSE c_avg;
   v_deptno:=r_emp.deptno;
  END IF;
  IF r_emp.sal < v_gemsal THEN
   v_oud_sal:=r_emp.sal;
   r_emp.sal := v_gemsal;
   V_nieuw_sal:=v_gemsal;
   per:= (v_gemsal-r_emp.sal)/r_emp.sal*100;
  UPDATE emp set sal=r_emp.sal where current of c_emp;
  INSERT INTO emp_audit VALUES(r_emp.empno,sysdate,v_oud_sal,v_nieuw_sal,per);
  END IF;
 END LOOP;
 END;
/
