create or replace procedure modify_customer_batch as
/*****************************************************
* 修改商户批次状态customserBatch
* authorization: xianyin.li
* last modify time: 2016-12-26
******************************************************/
v_remit_bill_total_count customer_batch.total_count%type;--付款批次总记录数
v_customer_batch_count customer_batch.total_count%type;--商户批次总笔数
v_customer_batch_id customer_batch.id%type; --商户id
--声明游标
cursor customer_batch_cursor is
select ctb.id, ctb.total_count
from customer_batch ctb
where ctb.status = 'PROCESSING';
begin
open customer_batch_cursor;
loop
--循环游标
fetch customer_batch_cursor
into v_customer_batch_id, v_customer_batch_count;
exit when customer_batch_cursor%notfound;
begin
--查询商户批次总笔数是否存在
if v_customer_batch_count = 0 then
goto point_1;
end if;
--查询付款批次处理的总记录数
select count(*)
into v_remit_bill_total_count
from remit_bill rb
where rb.customer_batch_id = v_customer_batch_id
and rb.batch_status = 'PROCESSING';
if v_remit_bill_total_count = v_customer_batch_count then
--修改商户批次状态
update customer_batch ctb
set ctb.status = 'SUCCESS'
where ctb.id = v_customer_batch_id
and ctb.status = 'PROCESSING';
end if;
<<point_1>>
commit;
end;
end loop;
close customer_batch_cursor;
end;
/*****************************************************
* 修改商户批次状态customserBatch
* authorization: xianyin.li
* last modify time: 2016-12-26
******************************************************/
v_remit_bill_total_count customer_batch.total_count%type;--付款批次总记录数
v_customer_batch_count customer_batch.total_count%type;--商户批次总笔数
v_customer_batch_id customer_batch.id%type; --商户id
--声明游标
cursor customer_batch_cursor is
select ctb.id, ctb.total_count
from customer_batch ctb
where ctb.status = 'PROCESSING';
begin
open customer_batch_cursor;
loop
--循环游标
fetch customer_batch_cursor
into v_customer_batch_id, v_customer_batch_count;
exit when customer_batch_cursor%notfound;
begin
--查询商户批次总笔数是否存在
if v_customer_batch_count = 0 then
goto point_1;
end if;
--查询付款批次处理的总记录数
select count(*)
into v_remit_bill_total_count
from remit_bill rb
where rb.customer_batch_id = v_customer_batch_id
and rb.batch_status = 'PROCESSING';
if v_remit_bill_total_count = v_customer_batch_count then
--修改商户批次状态
update customer_batch ctb
set ctb.status = 'SUCCESS'
where ctb.id = v_customer_batch_id
and ctb.status = 'PROCESSING';
end if;
<<point_1>>
commit;
end;
end loop;
close customer_batch_cursor;
end;