17,086
社区成员
发帖
与我相关
我的任务
分享
declare
v_totall_m number; ----可分配金额
begin
for c1 in (select distinct id from t) loop
select data1 into v_totall_m from t where t.id = c1.id and rownum <2;
for v_c in (select accno,data1 from t where t.id = c1.id order by date asc) loop
if v_totall_m - v_c.data1 > 0 then
update t set data3 = v_c.data1 where accno = v_c.accno and id = c1.id;
v_totall_m:= v_totall_m - v_c.data1;
else
update t set data3 = v_totall_m where accno = v_c.accno and id = c1.id;
goto l1;
end if;
end loop;
<<l1>>
commit;
end loop;
end;
select data1 into v_totall_m from t ;
declare
v_totall_m number; ----可分配金额
begin
for c1 in (select distinct id from t) loop
select data2 from t into v_totall_m;
for v_c in (select accno,data1 from t where t.id = c1.id order by date asc) loop
if v_totall_m - v_c.data1 > 0 then
update t set data3 = v_c.data1 where accno = v_c.accno and id = c1.id;
v_totall_m:= v_totall_m - v_c.data1;
else
update t set data3 = v_totall_m where accno = v_c.accno and id = c1.id;
goto l1;
end if;
end loop;
<<l1>>
commit;
end loop;
end;
declare
v_totall_m number; ----可分配金额
begin
for c1 in (select distinct id from t) loop
select data1 from t into v_totall_m;
for v_c in (select accno,data2 from t where t.id = c1.id order by date asc) loop
if v_totall_m - v_c.data2 > 0 then
update t set data3 = v_c.data2 where accno = v_c.accno and id = c1.id;
v_totall_m:= v_totall_m - v_c.data2;
else
update t set data3 = v_totall_m where accno = v_c.accno and id = c1.id;
goto l1;
end if;
end loop;
<<l1>>
commit;
end loop;
end;