生产管理软件开发：一种生成锯齿形产品BOM的方法

用MySQL 递归方法with recursive，编写一存储过程，生成锯齿形产品BOM。参数p_product_no是一个父项物料编码。

/* 
锯齿形产品BOM显示程序PROCEDURE ident_bom(p_product_no)
programed by fuerquan,copy right 2023,2030
*/
CREATE PROCEDURE `ident_bom`(p_product_no varchar(20))
BEGIN
	declare v_qty_per decimal(6,2) default 1.0;
	declare v_wastage_rate decimal(4,2) default 0.0;
    declare v_ancestry_no varchar(100) default p_product_no;
	with recursive temp_bom(lev,ancestry_no, item_no, item_name, 
            speci, item_type, qty_per, measure, procuremode, 
            lead_time,wastage_rate,scrap_rate)
	as (
		select 0,v_ancestry_no, item_no, item_name, speci, 
                item_type, v_qty_per, measure, procuremode, 
                lead_time,v_wastage_rate,scrap_rate 
			from items
			where item_no=p_product_no
		union all 
		select lev+1,concat(t.ancestry_no,':',b.component_no), 
			    b.component_no, b.component_name,b.speci, 
                b.component_type,b.qty_per,b.measure,b.procuremode, 
                t.lead_time+b.lead_time,b.wastage_rate, b.scrap_rate
        from temp_bom as t, v_bills as b 
		where (b.item_no = t.item_no) and t.lev = lev and lev <= 10
		)
	select concat(repeat('..',lev),item_no)  as 物料编码, item_name as 名称, 
           speci as 规格, item_type as 类型, measure as 单位, qty_per as 单用量, 
		    case procuremode when 0 then "制造" when 1 then "采购" 
            when 2 then "外协" end AS 获取方式,
            lead_time as 提前期,ifnull(scrap_rate,0.0) as 废品率, 
            ifnull(wastage_rate,0) as 损耗率
    from temp_bom order by ancestry_no;
END