oracle游标添加数据,Oracle使用游标更新数据

1. 使用游标修改数据

定义一个游标，游标名称为 mycursor

更新scott用户中emp表中empno为7369的销售额

-- Created on 2015/11/30 by ZHANW

declare

he emp%rowtype;

cursor mycursor(pid integer) is select * from emp where empno = pid for update;

begin

open mycursor(7369);

while(true) loop

fetch mycursor into he;

exit when mycursor%notfound;

update emp set sal = 1111 where current of mycursor;

end loop;

end;

-- Created on 2015/11/30 by ZHANW

declare

he emp%rowtype;

cursor mycursor(pid integer) is select * from emp where empno = pid for update;

begin

open mycursor(7369);

while(true) loop

fetch mycursor into he;

exit when mycursor%notfound;

delete from emp where current of mycursor;

end loop;

end;

注意：delete语句一定要写在exit后面，不然可能会报错。

优化：在定义游标时，可以在for update 后面添加 of 字段或者nowait。