useful-qry/function-in-db/procedures/point/credit_bd_sync.sql

drop procedure if exists credit_bd_sync;

DELIMITER //
create procedure credit_bd_sync( _sdate varchar(8), _edate varchar(8) )

SQL SECURITY INVOKER
BEGIN

Declare _line_id int;

Declare _not_found boolean;

Declare _line_cur_sales cursor for 
    select sobd.id 
	from dbr_sorder  as sohd
		inner join dbr_sorder_bd as sobd on sohd.id = sobd.sorder_id
		inner join dbr_item as itm on itm.id = sobd.item_id
	where sorder_date between _sdate and _edate 
	order by sorder_date asc, sobd.id asc;

Declare _line_cur_credit cursor for 
    select crdt.id 
	from dbr_credit as crdt
	where credit_date between _sdate and _edate 
	order by credit_date asc, crdt.id asc;

Declare continue handler for 
        not found set _not_found = true;

-- 지우는 범위는 avail_date가 아니다. 전표발생일자 기준
delete from dbr_credit_bd where occur_date between _sdate and _edate;

set _not_found = false;  -- 이것이 Multi cursor를 쓰는 핵심이다.
open _line_cur_sales;
	loop_sales: loop
		fetch _line_cur_sales into _line_id;
		if _not_found then leave loop_sales; end if;

		call credit_bd_act('sordbd', _line_id, 0);
	end loop loop_sales;
close _line_cur_sales;

set _not_found = false;  -- 이것이 Multi cursor를 쓰는 핵심이다.
open _line_cur_credit;
	loop_credit: loop
		fetch _line_cur_credit into _line_id;
		if _not_found then leave loop_credit; end if;

		call credit_bd_act('credit', _line_id, 0);
	end loop loop_credit;
close _line_cur_credit;

END;
//
DELIMITER ;