
SET SERVEROUTPUT ON
DECLARE
        TYPE t_ManagerTable IS TABLE OF employeez.mgr%TYPE
          INDEX BY BINARY_INTEGER;

        v_ManagerIdTable t_ManagerTable;

        CURSOR c_Employee1 IS
          SELECT empno, sal, mgr FROM employeez
          ORDER BY mgr
          FOR UPDATE OF sal;
        v_EmployeeId employeez.empno%TYPE;
        v_ManagerId employeez.mgr%TYPE;
        v_Salary employeez.sal%TYPE;
        v_FoundFlag BOOLEAN;
        v_LoopCnt BINARY_INTEGER;

BEGIN
        -- Build a table of manager ID's
        OPEN c_Employee1;
        FETCH c_Employee1 into v_EmployeeId, v_Salary, v_ManagerId;
        WHILE c_Employee1%FOUND LOOP
          IF v_ManagerIdTable.COUNT = 0

           OR v_ManagerId !=
             v_ManagerIdTable(v_ManagerIdTable.COUNT) THEN
             v_ManagerIdTable(v_ManagerIdTable.COUNT + 1) :=
             v_ManagerId;
          END IF;
          FETCH c_Employee1 into v_EmployeeId, v_Salary, v_ManagerId;
        END LOOP;
        CLOSE c_Employee1;

        -- Look in manager ID table to determine if employee is a manager
        -- If employee is a manager increase pay by 10% else by 2%

       OPEN c_Employee1;
       FETCH c_Employee1 into v_EmployeeId, v_Salary, v_ManagerId;
       WHILE c_Employee1%FOUND LOOP
          v_FoundFlag := FALSE;
          v_LoopCnt := 1;
          WHILE (v_LoopCnt <= v_ManagerIdTable.COUNT) AND
           (v_FoundFlag = FALSE) LOOP
            IF v_EmployeeId = v_ManagerIdTable(v_LoopCnt) THEN
              v_FoundFlag := TRUE;
            END IF;
            v_LoopCnt := v_LoopCnt + 1;
          END LOOP;
          IF v_FoundFlag = TRUE THEN
            v_Salary := 1.1 * v_Salary;
          ELSE
            v_Salary := 1.02 * v_Salary;
          END IF;
          UPDATE employeez
            SET sal = v_Salary
            WHERE CURRENT OF c_Employee1;
          FETCH c_Employee1 into v_EmployeeId, v_Salary, v_ManagerId;
        END LOOP;

        COMMIT;
END;
/
SET SERVEROUTPUT OFF
