PL/SQL-更新某员工工资，加薪金额错误
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13393215/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
PL/SQL-updating salary of an employee, the amount of increase in salary was wrong
提问by Katherine
I need to update the salary of employees from department 40 and 70. All employees from department 40 will have a 10% increase while employees from department 70 will have 15% increase.
我需要更新部门 40 和 70 员工的工资。部门 40 的所有员工将增加 10%，而部门 70 的员工将增加 15%。
I have 1 employee from department 70 who has a salary of 10000 so he will have a 15% increase. I expect his salary to become 11500, but it becomes 13225. I can't understand why. The employee from department 40 have the correct salary increase, only this one from department 70 is wrong.
我有 70 部门的 1 名员工，他的薪水为 10000，因此他将增加 15%。我预计他的工资会变成11500，结果变成了13225。我不明白为什么。部门 40 的员工加薪是正确的，只有部门 70 的员工加薪是错误的。
here is the pl/sql block..
这是 pl/sql 块..
SET serveroutput ON
DECLARE
CURSOR cur_emp
IS
SELECT * FROM employees WHERE department_id = 40 OR department_id = 70;
rec_emp cur_emp%rowtype;
BEGIN
OPEN cur_emp;
LOOP
FETCH cur_emp INTO rec_emp;
IF rec_emp.department_id = 40 THEN
UPDATE employees
SET salary = salary + (salary * 0.1)
WHERE employee_id = rec_emp.employee_id;
elsif rec_emp.department_id = 70 THEN
UPDATE employees
SET salary = salary + (salary * 0.15)
WHERE employee_id = rec_emp.employee_id;
END IF;
EXIT
WHEN cur_emp%notfound;
END LOOP;
CLOSE cur_emp;
END;
/
Could anyone help me figure out the problem with this one? thanks
谁能帮我找出这个问题？谢谢
回答by a_horse_with_no_name
No need for a stored procedure:
不需要存储过程：
update employees
set salary = case
when department_id = 40 then salary * 1.10
when department_id = 70 then salary * 1.15
else salary -- not strictly necessary. just to make sure.
end
where department_id in (40,70);
If you insist on doing it the slow way (a loop in PL/SQL) using an UPDATE ... WHERE CURRENT OFwould probably be faster than "unrelated" updates.
如果您坚持以缓慢的方式（PL/SQL 中的循环）使用 anUPDATE ... WHERE CURRENT OF可能比“无关”更新更快。
The real problem with your code is that you are leaving the loop "too late". Even if the cursor does not return anything after the fetch, you still do the update. You should put the EXIT WHEN ...beforethe IF clause and the update.
你的代码的真正问题是你离开循环“太晚了”。即使游标在获取后没有返回任何内容，您仍然会进行更新。你应该把EXIT WHEN ...之前的IF子句和更新。
DECLARE
CURSOR cur_emp
IS
SELECT * FROM employees WHERE department_id = 40 OR department_id = 70;
rec_emp cur_emp%rowtype;
BEGIN
OPEN cur_emp;
LOOP
FETCH cur_emp INTO rec_emp;
EXIT WHEN cur_emp%notfound; -- **** leave the loop right here, BEFORE doing the update *****
IF rec_emp.department_id = 40 THEN
UPDATE employees
SET salary = salary + (salary * 0.1)
WHERE employee_id = rec_emp.employee_id;
elsif rec_emp.department_id = 70 THEN
UPDATE employees
SET salary = salary + (salary * 0.15)
WHERE employee_id = rec_emp.employee_id;
END IF;
END LOOP;
CLOSE cur_emp;
END;
/
A more efficient way would be to use an updatable cursor though:
更有效的方法是使用可更新的游标：
DECLARE
CURSOR cur_emp
IS
SELECT department_id, salary
FROM employees
WHERE department_id in (40,70)
FOR UPDATE OF salary;
rec_emp cur_emp%rowtype;
new_sal number(12,2);
BEGIN
OPEN cur_emp;
LOOP
FETCH cur_emp INTO rec_emp;
EXIT WHEN cur_emp%NOTFOUND;
IF rec_emp.department_id = 40 THEN
new_sal := rec_emp.salary * 1.10;
elsif rec_emp.department_id = 70 THEN
new_sal := rec_emp.salary * 1.15;
END IF;
UPDATE employees
SET salary = new_sal
WHERE CURRENT OF cur_emp;
END LOOP;
CLOSE cur_emp;
END;
/
The use of the WHERE CURRENT OFwill actually reveal you error more clearly, because the loop will fail with an invalid rowidif you put the exitafterthe update.
的使用WHERE CURRENT OF实际上会更清楚地揭示您的错误，因为invalid rowid如果您在更新exit之后放置，循环将失败。
