Oracle SQL游标以提高薪资，直到达到最大金额

Question

对于这个问题，我需要把employees.salary提高20%，从最低工资开始，直到100,000美元用完为止。我很难找到一个解决方案，如何保存更新的金额，直到100,000美元已经使用。这就是我到目前为止所拥有的。谢谢

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;
/

Answer 1

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;
/

Answer 2

declare
 profits     number := 100000;
 tempinc     number(8,2);
begin
for hike_loop in (select employee_id,salary from employees order by salary asc) loop
if profits <= 0 then
break;
end if;
tempinc := hike_loop.salary * 0.20;
if (tempinc <= profits) then
update employees set salary = salary * 1.20 where employee_id = hike_loop.employee_id;
profits := profits - tempinc;
else
break;
end if;
end loop;
end;
/

Answer 3

只是为了好玩:下面是如何在普通SQL中这样做的。这个任务不需要函数或过程，除非它是PL/SQL类中的作业。即使这样，也应该从PL/SQL代码中运行相同的MERGE语句，这样处理就可以在设置的级别上完成，而不是逐行执行。

这也解决了“小的剩余金额”不同于目前公布的解决方案。如果没有足够的钱来增加“最后”的工资20%，那么它就会增加多少，但是剩下的却是100,000美元。如果两名或两名以上工资相同的雇员是“第一个被排除在外的人”，则“剩余数额”在这些雇员之间平均分配。

merge into employees e
  using ( select employee_id,
                 sum  (salary) over (order by salary)     as sum_salary,
                 count(salary) over (partition by salary) as cnt
          from   employees
        ) x
    on ( e.employee_id = x.employee_id )
when matched then update
  set salary = 1.2 * salary + case when sum_salary <= 100000 / 0.2 then 0
                                   else (100000 - 0.2 * sum_salary) / cnt end  
    where sum_salary - cnt * salary <= 100000 / 0.2
;