declare
money1 decimal(18,2);
money2 decimal(18,2);
cursor tb2 is select year ,code ,entcode,mon from accsum where mon <> 1 order by mon ;
begin
for i in begin
select mdebsum,mcresum into money1,money2 from a_fykjhszxcw.ds_accsum
where code=i.code and entcode=i.entcode and mon=i.mon-1 and year =i.year;
update accsum set mdebsum=money1+mdeb,mcresum=money2+mcre
where entcode = i.entcode and year = i.year and code = i.code and mon=i.mon;
commit; end;
end loop;
end;
现有代码如上 不过数据量很大 更新很慢 有没有方法可以快速更新呀? 用途是金额的累计
money1 decimal(18,2);
money2 decimal(18,2);
cursor tb2 is select year ,code ,entcode,mon from accsum where mon <> 1 order by mon ;
begin
for i in begin
select mdebsum,mcresum into money1,money2 from a_fykjhszxcw.ds_accsum
where code=i.code and entcode=i.entcode and mon=i.mon-1 and year =i.year;
update accsum set mdebsum=money1+mdeb,mcresum=money2+mcre
where entcode = i.entcode and year = i.year and code = i.code and mon=i.mon;
commit; end;
end loop;
end;
现有代码如上 不过数据量很大 更新很慢 有没有方法可以快速更新呀? 用途是金额的累计
那个游标遍历应该可以优化下
如果数据量很大的话,更新速度很慢,可以重建表
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:6407993912330
declare
cursor tb2 is
select a.year,a.code,a.entcode,a.mon,
sum(b.mdebsum) mdebsum ,sum(b.mcresum) mcresum
from accsum a,a_fykjhszxcw.ds_accsum b
where a.mon <> 1
and a.code=b.code
and a.entcode=b.entcode
and b.mon=a.mon-1
and a.year =b.year
group by a.year,a.code,a.entcode,a.mon;
begin
for i_tb2 in tb2 loop
update accsum
set mdebsum = i_tb2.mdebsum+mdeb,mcresum=i_tb2.mcresum+mcre
where entcode = i_tb2.entcode
and year = i_tb2.year
and code = i_tb2.code
and mon=i_tb2.mon;
end loop;
commit;
end;