Search code examples
sqloraclecursorprocedure

Oracle SQL Cursor to increase salaries until the max amount is reached


For this problem I need to increase employees.salary by 20% starting with the lowest salaries(asc order) until $100,000 is exhausted. I'm having difficulty finding a solution on how to save the updated amount left until $100,000 has been used. This is what I have so far. Thank you

declare
 cursor mancur is
   select salary from employees order by salary asc;
 tempcur     mancur%ROWTYPE;
 profits     number := 100000;
 tempsalary  employees.salary%type;
 tempinc     number(8,2);
begin
 open mancur;
 loop
   fetch mancur into tempcur;
   tempinc := tempcur.salary * 1.20;
   tempsalary := profits - tempcur.salary;
   dbms_output.put_line(tempcur.salary || ' increased by 20% ' || tempinc || ', Bonus amount left ' || tempsalary);
   exit when mancur%notfound; --when 100,000 has been used
   --update employees set salary = salary * 1.20 where employee_id = tempcur.employee_id;
 end loop;
 close mancur;
end;
/

Solution

  • begin
     open mancur;
      loop
       fetch mancur into tempcur;
       tempinc := tempcur.salary * 1.20;
       profits := profits - (tempinc-tempcur.salary);  -- You have to keep subtracting the increment amount to find if bonus is exhausted or not
          if profits <=0 Then  --When all your funds are exhausted
             Exit
          End if
                  dbms_output.put_line(tempcur.salary || ' increased by 20% ' || tempinc || ', Bonus amount left ' || profits);
          exit when mancur%notfound; --when 100,000 has been used
       --update employees set salary = salary * 1.20 where employee_id = 
          tempcur.employee_id;
       end loop;
     close mancur;
    end;
    /