Oracle SQL光标增加工资,直到达到最大数量
问题描述:
对于这个问题,我需要将雇员数量增加20%,以最低工资(asc订单)开始,直到耗尽100,000美元。我很难找到一个解决方案,如何保存更新的金额,直到使用$ 100,000。这是我迄今为止所拥有的。谢谢Oracle SQL光标增加工资,直到达到最大数量
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;
/
答
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;
/
+0
谢谢! Idk我怎么弄不懂。时间从屏幕休息一下哈 – namesjj
答
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;
/
答
只是为了好玩:这里是应如何在普通的SQL来完成。除非它是PL/SQL类中的作业,否则不需要此任务的函数或过程。即使如此,应该从PL/SQL代码运行相同的MERGE
语句,以便处理在设置的级别完成,而不是逐行完成。
这也解决了迄今为止发布的解决方案中的“小剩余量”问题。如果没有足够的“最后”工资增加20%,那么增加的幅度可能高达10万美元。如果两名或两名以上薪酬相同的员工是“第一个被排除在外”,那么“剩余数量”在这些员工之间平分。
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
;
这是生产问题还是作业问题?如果它是作业,是PL/SQL类吗?使用单个SQL语句可以更高效地解决此需求。 – mathguy
另外,你如何处理关系?假设你上了名单,现在下一个员工(他们的薪水还没有增加)是三名同样薪水的员工。他们目前的薪水是每人30,000美元,所以他们每人应该得到6,000美元,但只剩下12,000美元。谁得到了多少? – mathguy