Question

游标循环更新表的时候限制条件无效

以下是代码
declare
type jinwei is record(
xm varchar2(22),
jibengz number,
jiangjin number,
shifagz number
);
cursor jwrq_cur is
select xm,jibengz,jiangjin,shifagz from zy_gz for update ;
begin
for jinwei in jwrq_cur loop
if jinwei.shifagz<2000 then update zy_gz set
beizhu='低收入';
end if;
end loop;
end;

总是整张表的备注都给我改成低收入，我也是醉了。除非我在update后面加入限制shifagz<2000
但是那样的话，我做这个游标还有啥意义呢……直接update就行了，初学者哈，想不明白这里的意义

Answer 1

cursor jwrq_cur is
select xm,jibengz,jiangjin,shifagz from zy_gz for update ;
begin
for jinwei in jwrq_cur loop
if jinwei.shifagz<2000 then update zy_gz set
beizhu='低收入';
end if;
end loop;
end;
改为
cursor jwrq_cur is
select xm,jibengz,jiangjin,shifagz,rowid from zy_gz for update ;
begin
for jinwei in jwrq_cur loop
if jinwei.shifagz<2000 then update zy_gz set
beizhu='低收入' where rowid=jinwei.rowid ;
end if;
end loop;
end;

加个rowid限制条件就行了。不然每次循环你都会更新一次zy_gz这个表，设置beizhu='低收入'。