credit_bd_sync.sql 1.4 KB

12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152535455
  1. drop procedure if exists credit_bd_sync;
  2. DELIMITER //
  3. create procedure credit_bd_sync( _sdate varchar(8), _edate varchar(8) )
  4. BEGIN
  5. Declare _line_id int;
  6. Declare _not_found boolean;
  7. Declare _line_cur_sales cursor for
  8. select sobd.id
  9. from dbr_sorder as sohd
  10. inner join dbr_sorder_bd as sobd on sohd.id = sobd.sorder_id
  11. inner join dbr_item as itm on itm.id = sobd.item_id
  12. where sorder_date between _sdate and _edate
  13. order by sorder_date asc, sobd.id asc;
  14. Declare _line_cur_credit cursor for
  15. select crdt.id
  16. from dbr_credit as crdt
  17. where credit_date between _sdate and _edate
  18. order by credit_date asc, crdt.id asc;
  19. Declare continue handler for
  20. not found set _not_found = true;
  21. -- 지우는 범위는 avail_date가 아니다. 전표발생일자 기준
  22. delete from dbr_credit_bd where occur_date between _sdate and _edate;
  23. set _not_found = false; -- 이것이 Multi cursor를 쓰는 핵심이다.
  24. open _line_cur_sales;
  25. loop_sales: loop
  26. fetch _line_cur_sales into _line_id;
  27. if _not_found then leave loop_sales; end if;
  28. call credit_bd_act('sordbd', _line_id, 0);
  29. end loop loop_sales;
  30. close _line_cur_sales;
  31. set _not_found = false; -- 이것이 Multi cursor를 쓰는 핵심이다.
  32. open _line_cur_credit;
  33. loop_credit: loop
  34. fetch _line_cur_credit into _line_id;
  35. if _not_found then leave loop_credit; end if;
  36. call credit_bd_act('credit', _line_id, 0);
  37. end loop loop_credit;
  38. close _line_cur_credit;
  39. END;
  40. //
  41. DELIMITER ;