create or replace procedure update_acco_order
as
--定义变量
v_borr_person varchar2(40);
v_proid varchar2(40);
v_borr_amou number(20, 2);
v_which varchar2(40);
v_orderno varchar2(40);
v_ordermoney number(20, 2);
begin
--定义游标
declare cursor mycusor is select appl_user from web_p2p_get_bash where status = 0 and buss_type is null;
--开始使用游标取数据
begin
open mycusor;
loop
fetch mycusor into v_borr_person;
--游标取不到数据则退出
exit when mycusor%notfound;
select t.pro_code into v_proid from web_p2p_person_pro t
where exists (select tt.* from web_secr_cert tt where tt.user_code = v_borr_person and tt.ident = t.iden_no);
select borr_amou*100 into v_borr_amou from web_p2p_subj_matt_prod where code = v_proid;
while v_borr_amou >0 loop
select code, order_no, amount into v_which, v_orderno, v_ordermoney from
( select * from order_account_detail
where account_code ='STRUCT_USER_CODE' and amount > 0
order by add_time)
where rownum = 1 ;
if v_ordermoney >= v_borr_amou then
update order_account_detail t
set t.amount = t.amount - v_borr_amou, t.update_time = sysdate
where t.code = v_which;
insert into order_account_detail (code, order_no, account_code, amount, add_time, pro_code)
values (create_guid(), v_orderno, v_borr_person, v_borr_amou, sysdate, v_proid);
v_borr_amou := 0;
else
update order_account_detail t
set t.amount =0, t.update_time = sysdate
where t.code = v_which;
insert into order_account_detail (code, order_no, account_code, amount, add_time, pro_code)
values (create_guid(), v_orderno, v_borr_person, v_ordermoney, sysdate, v_proid);
v_borr_amou := v_borr_amou - v_ordermoney;
end if;
end loop;
end loop;
close mycusor;
end;
end update_acco_order;