按行資料累加計算

create table test(
km varchar2(10), --會計科目
dzrq date, --登帳日期
jfje number(18,2), --借方金額
dfje number(18,2), --貸方金額
ye number(18,2) default 0 --餘額
);

insert into test(km,jfje,dfje,dzrq) values('1',300,500,to_date('2006-01-01','yyyy-mm-dd'));
insert into test(km,jfje,dfje,dzrq) values('1',600,800,to_date('2006-01-02','yyyy-mm-dd'));
insert into test(km,jfje,dfje,dzrq) values('2',300,600,to_date('2006-01-01','yyyy-mm-dd'));
insert into test(km,jfje,dfje,dzrq) values('3',700,500,to_date('2006-01-01','yyyy-mm-dd'));
insert into test(km,jfje,dfje,dzrq) values('2',600,500,to_date('2006-01-02','yyyy-mm-dd'));
insert into test(km,jfje,dfje,dzrq) values('2',800,500,to_date('2006-01-03','yyyy-mm-dd'));
insert into test(km,jfje,dfje,dzrq) values('3',300,500,to_date('2006-01-02','yyyy-mm-dd'));

commit;

create or replace procedure p_test
is
cursor cur_mxz is
select rowid,km,dzrq,jfje,dfje,ye
from test
order by km,dzrq;
row_old cur_mxz%rowtype;
begin
row_old.km := 'initial km';
for row_mxz in cur_mxz loop
--if cur_mxz%rowcount>1 then
if row_mxz.km != row_old.km then
row_mxz.ye := row_mxz.jfje - row_mxz.dfje;
else
--大量帶小數位的資料累加等於1資料庫崩潰,oracle的bug,採用先乘100再除100迴避
row_mxz.ye := round((row_old.YE * 100 + row_mxz.JFJE * 100 - row_mxz.DFJE * 100)/100, 2);
end if;

update test set ye = row_mxz.ye
where rowid = row_mxz.rowid;

row_old := row_mxz;
end loop;
commit;
exception
when others then
RAISE_APPLICATION_ERROR(-20000, '累加處理錯誤', TRUE);
end p_test;
/

exec p_test;

select * from test order by km,dzrq;

[@more@]