生产管理软件算法交流：用递归方法计算物料毛需求的MySQL存储过程

-- 用递归方法基于bom展开一个主计划项目，获得各子项物料的毛需求和需求日期。

-- p_mpsid是主计划项目id，master_no是主计划项目编号，p_maxlev是最大展开层数。

-- date_subreq是自定义函数，用于计算子项的基于工厂日历的需求日期。

CREATE DEFINER=dbuser@% PROCEDURE decompose(p_mpsid int, p_maxlev) BEGIN insert into raws(mps_id, master_no, item_no, name, qty, lead_time, date_req, lev) with recursive gross_required (mps_id, master_no, item_no, name, qty, lead_time, date_req, lev)         as (         select m.mps_id, m.master_no, m.product_no, m.product_name, m.qty, i.lead_time, m.finish_date_plan, 1         from master_schedules as m,items as i

        where i.item_no=m.product_no and mps_id = p_mpsid         union all         select g.mps_id, g.master_no, b.component_no, b.component_name, g.qty*b.qty_per, b.lead_time, date_subreq(g.date_req, g.lead_time), lev+1         from gross_required g, v_bom b         where (b.item_no = g.item_no) and g.lev = lev and lev