sql - PL/SQL-updating salary of an employee,加薪金额不对

Question

我需要更新部门 40 和 70 员工的工资。部门 40 的所有员工将增加 10%，而部门 70 的员工将增加 15%。

我有 1 名来自部门 70 的员工，他的薪水为 10000，因此他将增加 15%。我期望他的工资变成11500，结果变成了13225，我不明白为什么。 40部门员工加薪正确，只有70部门员工加薪错误。

这里是 pl/sql block ..

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

谁能帮我解决这个问题？谢谢

Answer 1

不需要存储过程:

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

如果您坚持使用 UPDATE ... WHERE CURRENT OF 以慢速方式(PL/SQL 中的循环)执行此操作，可能会比“无关”更新更快。

您的代码的真正问题是您离开循环“太晚了”。即使游标在获取之后没有返回任何内容，您仍然会进行更新。您应该将 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;
/

不过，更有效的方法是使用可更新游标:

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

WHERE CURRENT OF 的使用实际上会更清楚地揭示您的错误，因为如果您将 exit 更新后。