oracle存储过程

CREATE OR REPLACE procedure p_order_share( i_order_code       in varchar2, --订单编号
                                           o_code              out varchar2, --返回编码
                                           o_remarks           out varchar2 --返回描述
                                           ) as
  v_actual_price number(18,2); --实收金额
  v_deposit number(18,2);   --押金总额
  v_tran_price number(18,2); -- 运输费用
  v_user_id varchar2(50); --下单人
  v_wx_pay_amount number(18,2); --微信支付金额
  v_wx_sum_share_price number(18,2); --微信总分摊金额
  v_jb_pay_amount number(18,2); --京币支付金额
  v_jb_sum_share_price number(18,2); --京币总分摊金额
  v_order_goods_count number(10); --订单商品数量
  v_add_share_price number(18,2); --累计分摊金额
  v_current_share_price number(18,2); --当前分摊金额
  v_current_sum_share_price number(18,2); --当前分摊总金额
  v_i number(10);           --循环序列
  v_vip_flag integer;   --会员标识
  v_current_score number(18,2);   --当前可用积分
  v_current_total_score number(18,2);     --当前累计总积分
  v_add_score number(18,2);       --累计获得积分          
begin
  --初始化金额
  v_actual_price := 0;
  v_deposit := 0;
  v_tran_price := 0;
  v_wx_pay_amount := 0;
  v_wx_sum_share_price := 0;
  v_jb_pay_amount := 0;
  v_jb_sum_share_price := 0;
  v_order_goods_count := 0;
  v_add_score := 0;
  --获取订单信息
  select oi.actual_price,oi.deposit,oi.tran_price,oi.user_info_id into v_actual_price,v_deposit,v_tran_price,v_user_id from order_info oi where oi.order_code = i_order_code; 
  --获取用户信息
  select nvl(ui.score,0),nvl(ui.total_score,0),ui.vip_flag into v_current_score,v_current_total_score,v_vip_flag from user_info ui where ui.id = v_user_id;                               
  --获取订单商品数量
  select count(*) into v_order_goods_count from order_details od where  od.order_code =  i_order_code;
  --获取微信支付金额
  select nvl(sum(opr.pay_amount),0) into v_wx_pay_amount from order_pay_record opr where opr.order_code = i_order_code and opr.pay_type = 1;
  --获取京币支付金额
  select nvl(sum(opr.pay_amount),0) into v_jb_pay_amount from order_pay_record opr where opr.order_code = i_order_code and opr.pay_type = 2;
  --不同支付分摊金额计算
  --押金总额大于0
  if v_deposit > 0 then
    --押金总额小于等于京币支付金额
    if v_deposit <= v_jb_pay_amount then
      v_jb_sum_share_price := v_jb_pay_amount - v_deposit;
      v_wx_sum_share_price := v_wx_pay_amount;
    else
      v_jb_sum_share_price := 0;
      v_wx_sum_share_price := v_wx_pay_amount - (v_deposit - v_jb_pay_amount);
    end if;
  else
    v_jb_sum_share_price := v_jb_pay_amount;
    v_wx_sum_share_price := v_wx_pay_amount;
  end if;
  --定义游标
  declare
      --支付游标
      cursor payCur is select opr.id,opr.pay_type,opr.pay_amount,opr.order_code from order_pay_record opr where opr.order_code = i_order_code;
      --订单详情游标
      cursor orderDetailsCur is select od.goods_details_id,od.actual_price,od.amount,nvl(dg.deposit,0) as deposit from order_details od left join deposit_goods dg on od.deposit_goods_id = dg.id where  od.order_code =  i_order_code;
      --押金游标
      cursor depositCur is select od.order_code,od.goods_details_id,od.goods_name,od.deposit_goods_id,sum(od.amount) as amount,sum(dg.deposit) as deposit ,min(dg.term) as term from order_details od ,deposit_goods dg where od.deposit_goods_id = dg.id and od.order_code =  i_order_code group by od.order_code,od.goods_details_id,od.goods_name, od.deposit_goods_id,dg.term;
      --积分游标
      cursor scoreCur is select od.actual_price,nvl(gd.score,0) as score,nvl(gd.score_vip,0) as score_vip,od.id,od.amount,od.goods_details_id from order_details od ,goods_details gd where od.goods_details_id = gd.id and od.order_code = i_order_code ;
  begin
    --循环支付游标
    for pay in payCur loop
      --初始化累计分摊金额
      v_add_share_price := 0;
      --初始化当前分摊总金额
      v_current_sum_share_price := 0;
      --判断微信支付并且总分摊金额大于0
      if pay.pay_type = 1 and v_wx_sum_share_price > 0 then
        v_current_sum_share_price := v_wx_sum_share_price;
      --判断京币支付并且总分摊金额大于0
      elsif pay.pay_type = 2 and v_jb_sum_share_price > 0 then
        v_current_sum_share_price := v_jb_sum_share_price;
      end if;
      --循环订单详情游标
      for orderDetails in orderDetailsCur loop
           --初始化当前分摊金额
           v_current_share_price := 0;
           --判断只有一个商品
           if v_order_goods_count = 1 then
              v_current_share_price := v_current_sum_share_price;
           --判断是否最后一个分摊商品
           elsif orderDetailsCur%rowCount = v_order_goods_count then
              v_current_share_price := v_current_sum_share_price - v_add_share_price;
           else
             v_current_share_price := orderDetails.actual_price/(v_wx_sum_share_price + v_jb_sum_share_price) * v_current_sum_share_price;
             v_add_share_price := v_add_share_price + v_current_share_price;
           end if;
           --插入分摊数据
           insert into order_details_share (id, order_code, order_pay_id, order_details_id, share_amount, num, createid, createtime)
           values (sys_guid(), pay.order_code, pay.id, orderDetails.goods_details_id, v_current_share_price, orderDetails.amount, v_user_id, sysdate);
      end loop;
    end loop;
    --循环押金游标
    for deposit in depositCur loop
      v_i := 0;
      if deposit.deposit > 0 then
        for v_i in 1..deposit.amount loop
          insert into order_deposit (id, order_code,goods_details_id,goods_name, deposit_goods_id,amount, jb_amount, wx_amount, num, end_date, status, createid, createtime)
          values (sys_guid(), deposit.order_code,deposit.goods_details_id,deposit.goods_name, deposit.deposit_goods_id, deposit.deposit,0,0, 1, sysdate+deposit.term , 0, v_user_id, sysdate);  
        end loop;
      end if;
    end loop;
    --循环积分游标
    for score in scoreCur loop
      if v_vip_flag = 1 and score.score_vip <> 0 then 
        v_add_score := v_add_score + trunc(score.actual_price/score.score_vip);
        --更新订单明细积分数量
        update order_details od set od.gain_score = trunc(score.actual_price/score.score_vip) ,od.return_score = 0 where od.id = score.id;
      elsif v_vip_flag = 0 and score.score <> 0 then
        v_add_score := v_add_score + trunc(score.actual_price/score.score);
        --更新订单明细积分数量
        update order_details od set od.gain_score = trunc(score.actual_price/score.score) ,od.return_score = 0 where od.id = score.id;
      end if;
      --更新商品规格销售数量
      update goods_details gd set gd.spec_sales = nvl(gd.spec_sales,0) + score.amount where gd.id = score.goods_details_id;
    end loop;
    --判断获得积分总数量
    if v_add_score > 0 then 
      v_add_score := v_add_score;
      --插入积分明细
      insert into user_score_record (id, user_info_id, type, createtime, in_score, out_score, score, remarls)
      values (sys_guid(), v_user_id, 1, sysdate, v_add_score, 0, v_current_score + v_add_score, i_order_code);
      --更新账户积分数量
      update user_info ui set ui.total_score = ui.total_score + v_add_score ,ui.score = ui.score + v_add_score where ui.id = v_user_id;
    end if;
  end;
  o_code    := '200';
  o_remarks := '成功';
end;

 

后台调用接口：