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

drop procedure if exists reward_bd_sync;

DELIMITER //
create procedure reward_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_reward cursor for 
    select rewd.id 
	from dbr_reward as rewd
	where reward_date between _sdate and _edate 
	order by reward_date asc, rewd.id asc;

Declare _line_cur_sorder cursor for 
    select sohd.id 
	from dbr_sorder as sohd
	where sorder_date between _sdate and _edate 
	order by sorder_date asc, sohd.id asc;

Declare continue handler for 
        not found set _not_found = true;

-- 지우는 범위는 avail_date가 아니다. 전표발생일자 기준
delete from dbr_reward_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 reward_bd_act('sordbd', _line_id, 0);
	end loop loop_sales;
close _line_cur_sales;

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

		call reward_bd_act('reward', _line_id, 0);
	end loop loop_reward;
close _line_cur_reward;

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

		call reward_bd_act('sorder', _line_id, 0);
	end loop loop_sorder;
close _line_cur_sorder;

END;
//
DELIMITER ;