create or replace procedure modify_customer_batch as
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;
<>
commit;
end;
end loop;
close customer_batch_cursor;
end;