useful-qry/function-in-db/procedures/stock-io/stock_io_sync.sql

drop procedure if exists stock_io_sync;

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

BEGIN

Declare _line_id int;

Declare _not_found boolean;

Declare _line_cur_sales cursor for 
    select sabd.id 
	from dbr_sorder  as sohd
		inner join dbr_sorder_bd as sobd on sohd.id = sobd.sorder_id
		inner join dbr_sales_bd as sabd on sobd.id = sabd.sorder_bd_id
		inner join dbr_sales as sahd on sahd.id = sabd.sales_id
	where sales_date between _sdate and _edate 
	order by sales_date asc, sabd.id asc;

Declare _line_cur_purch cursor for 
    select pubd.id 
	from dbr_porder  as pohd
		inner join dbr_porder_bd as pobd on pohd.id = pobd.porder_id
		inner join dbr_purch_bd as pubd on pobd.id = pubd.porder_bd_id
		inner join dbr_purch as puhd on puhd.id = pubd.purch_id
	where purch_date between _sdate and _edate 
	order by purch_date asc, pubd.id asc;

Declare _line_cur_genio cursor for 
    select gnbd.id 
	from dbr_genio  as gnhd
		inner join dbr_genio_bd as gnbd on gnhd.id = gnbd.genio_id
	where genio_date between _sdate and _edate 	
	order by genio_date asc, gnbd.id asc;

Declare continue handler for 
        not found set _not_found = true;

delete from dbr_stock_io where io_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 stock_io_act('sales', _line_id, 0);
	end loop loop_sales;
close _line_cur_sales;

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

		call stock_io_act('purch', _line_id, 0);
	end loop loop_purch;
close _line_cur_purch;

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

		call stock_io_act('genio', _line_id, 0);
	end loop loop_genio;
close _line_cur_genio;

END;
//
DELIMITER ;