用游标实现,我觉得绝对这种方法比较安全的。
–首先定一个游标把需要用到的一些数据存放到游标中:
复制代码 代码如下:
declare
cursor d_cursor_cus_info is
select t3.id_ as id_,
t3.owe_money_ as owe_money_,
a.heatingarea as heating_area_
from t_cus_owe_money_2 t2
left join t_cus_owe_money_3 t3 on t2.id_= t3.id_
left join (select s.bh, sum(
case
when s.stkbz=’0′ then nvl(s.mj,0)
when s.stkbz=’1′ then 0-nvl(s.mj,0)
end
) as heatingarea from sk s where s.nd = ‘2008-2009’ group by s.bh) a on t2.bh_=a.bh
where t3.owe_money_- t2.owe_money_ = a.heatingarea*5 and t3.owe_money_ > 0;
–然后循环游标对数据进行更新:
复制代码 代码如下:
begin
for everyrow in d_cursor_cus_info
loop
update t_cus_year_status t
set t.heating_area_ = everyrow.heating_area_,
t.owe_money_ = everyrow.owe_money_
where t.year_ = ‘2008-2009’
and t.id_ = everyrow.id_;
end loop;
commit;
end;