存储过程中游标,条件判断,循环的使用

上一篇 / 下一篇  2016-06-27 14:56:58 / 个人分类:数据库

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;

TAG: SQL 存储过程 sql

引用 删除 ljf717   /   2016-06-29 14:45:56
5
 

评分:0

我来说两句

显示全部

:loveliness: :handshake :victory: :funk: :time: :kiss: :call: :hug: :lol :'( :Q :L ;P :$ :P :o :@ :D :( :)

Open Toolbar