oracle存储过程

create or replace procedure update_hb_order_state is
--商城卡
CURSOR funds_ssk--前一天商城卡支付金额
IS
select t.order_id as order_id,is_reconciliation
from hb_order_settle t where t.settle_type = 2
and to_char(t.settle_time, 'yyyy-MM-dd') = to_char(sysdate - 1, 'yyyy-MM-dd');

CURSOR cusor_a--前一天商城卡支付金额根据订单号分组
IS
select t.order_id as order_id
from hb_order_settle t where t.settle_type = 2
and to_char(t.settle_time, 'yyyy-MM-dd') = to_char(sysdate - 1, 'yyyy-MM-dd') group by t.order_id;

--兑换卡
cursor funds_ddk--前一天兑换卡支付金额
is
select t3.id as order_id
from hb_goods_card t1, hb_goods_card_type t2, hb_order t3
where to_char(t1.change_time, 'yyyy-MM-dd') = to_char(sysdate - 1, 'yyyy-MM-dd')
and t1.type_code = t2.type_code and t3.goods_card_num = t1.card_num;

--实物销售
cursor funds_swss--前一天的实物销售数据
is
select t.id as order_id from hb_order t where t.order_type = 4
and to_char(t.insert_time, 'yyyy-MM-dd') = to_char(sysdate - 1, 'yyyy-MM-dd');

recon number;--1未对账

begin
    --如果商城卡支付全部已对账则将主订单对账状态改为0
    for a in cusor_a loop
        --判断是否全部已对账
        for b in funds_ssk loop
            if a.order_id = b.order_id
            then
                if b.is_reconciliation <> 0--如果这组订单包含未对账的
                then 
                     recon := 1;
                end if;
            end if;
        end loop;
        if recon <> 1
        then
            update hb_order t set t.is_reconciliation = 0 where t.id = a.order_id;
        end if;
    end loop;
  
    --兑换卡
    FOR o IN funds_ddk LOOP
        update hb_order t set t.is_reconciliation = 0 where t.id = o.order_id;
    end LOOP;
    
    --实物销售
    FOR o IN funds_swss LOOP
        update hb_order t set t.is_reconciliation = 0 where t.id = o.order_id;
    end LOOP;
    commit;
end update_hb_order_state;