oracle 游标小例

有如下emp表结构：

create table (
id int,
user_name varchar2(20),
salary int,
emp_deptno int
);

插入数据如下：

insert into emp (ID, USER_NAME, SALARY, EMP_DEPTNO) values (1, 'Zhangsan', 1700, 10);

insert into emp (ID, USER_NAME, SALARY, EMP_DEPTNO) values (2, 'Lisi', 4300, 20);

insert into emp (ID, USER_NAME, SALARY, EMP_DEPTNO) values (3, 'Wangwu', 6600, 30);

insert into emp (ID, USER_NAME, SALARY, EMP_DEPTNO) values (4, 'Qianliu', 5100, 20);

insert into emp (ID, USER_NAME, SALARY, EMP_DEPTNO) values (5, 'Chenqi', 7500, 30);

commit;

现在需要对不同的deptno进行工资salary的增长，deptno = 10, salary增加100, deptno = 20, salary增加200, deptno = 3, salary 增加300, 其它情况下salary增加400.

错误的存储过程如下：

declare
  v_increment int;
  v_deptno    int;
  cursor emp_cursor is
    select * from emp;
begin
  for i_emp in emp_cursor loop
    v_deptno := i_emp.emp_deptno;
    case v_deptno
      when 10 then
        v_increment := 100;
      when 20 then
        v_increment := 200;
      when 30 then
        v_increment := 300;
      else
        v_increment := 400;
    end case;
    update emp
       set salary = salary + v_increment
     where emp_deptno = v_deptno;
  end loop;
end;
/

select * from emp;

这里存在了对emp_deptno相同的数据进行重复插入，故需要进行数据的去重，修改为：

declare
  v_increment int;
  v_deptno    int;
  cursor emp_cursor is
    select distinct emp_deptno from emp;
begin
  for i_emp in emp_cursor loop
    v_deptno := i_emp.emp_deptno;
    case v_deptno
      when 10 then
        v_increment := 100;
      when 20 then
        v_increment := 200;
      when 30 then
        v_increment := 300;
      else
        v_increment := 400;
    end case;
    update emp
       set salary = salary + v_increment
     where emp_deptno = v_deptno;
  end loop;
end;
/

select * from emp;

 这样的错误很容易犯，却不容易找到根源。

 有一种更好的方法是设置更新游标，如下：

declare
  v_increment int;
  v_deptno    int;
  cursor emp_cursor is
    select *  from emp for update of salary;
begin
  for i_emp in emp_cursor loop
    v_deptno := i_emp.emp_deptno;
    case v_deptno
      when 10 then
        v_increment := 100;
      when 20 then
        v_increment := 200;
      when 30 then
        v_increment := 300;
      else
        v_increment := 400;
    end case;
    update emp
       set salary = salary + v_increment
     where current of emp_cursor;
  end loop;
end;
/

select * from emp;