SQL版本 --SQL版本 DROP FUNCTION [IPS_BomList] GO CREATE FUNCTION [IPS_BomList]() RETURNS TABLE AS RETURN ( WITH BOMChild AS ( --获取产品数据 SELECT T0.Code AS ProductCode, T0.Name AS ProductName, CAST(NULL AS NVARCHAR(50)) AS FatherCode, CAST(NULL AS NVARCHAR(200)) AS FatherName, T0.Code AS ItemCode, T0.Name AS ItemName, T0.Qauntity AS Quantity, CAST(T0.Code AS NVARCHAR(500)) AS BomCode, 1 AS BomLevel, CAST('000' AS NVARCHAR(200)) AS LevelTag FROM OITT T0 WHERE 1=1 UNION ALL --递归获取子件 SELECT T2.ProductCode, T2.ProductName, T0.Code AS FatherCode, T0.Name AS FatherName, T1.Code AS ItemCode, T1.ItemName AS ItemName, T1.Quantity AS Quantity, CAST(REPLICATE(' ',T2.BomLevel)+T1.Code AS NVARCHAR(500)) AS BomCode, T2.BomLevel+1 AS BomLevel, CAST(T2.LevelTag+'.'+RIGHT('000'+CAST(T1.ChildNum AS NVARCHAR(10)),3) AS NVARCHAR(200)) AS LevelTag FROM OITT T0 INNER JOIN ITT1 T1 ON T1.Father=T0.Code INNER JOIN BOMChild T2 ON T2.ItemCode=T0.Code OR (T2.BomLevel IS NULL AND T0.Code = T2.ProductCode) ) SELECT ProductCode, ProductName, FatherCode, FatherName, ItemCode, ItemName, Quantity, BomCode, BomLevel, LevelTag FROM BOMChild ) HANA版本 --HANA版本 DROP PROCEDURE "IPS_BomList"; CREATE PROCEDURE "IPS_BomList"() AS BEGIN DECLARE CNT INTEGER; --获取所有产成品 TMP_Product= SELECT T0."Code" AS "ProductCode", T0."Name" AS "ProductName", T0."Code" AS "ItemCode", T0."Name" AS "ItemName", T0."Qauntity" AS "Quantity", CAST('000' AS NVARCHAR(200)) AS "BOM_Level" FROM "OITT" T0 ORDER BY T0."Code"; --获取所有BOM明细清单 TMP_BOMDetail= SELECT T0."Code" AS "ItemCode", T0."Name" AS "ItemName", T0."Qauntity" AS "Quantity", T1."Code" AS "ChildCode", T1."ItemName" AS "ChildName", T1."Quantity" AS "ChildQuantity", T1."ChildNum", CAST('000.'||RIGHT('000'||(T1."ChildNum"),3) AS NVARCHAR(200)) AS "BOM_Level" FROM OITT T0 INNER JOIN ITT1 T1 ON T1."Father"=T0."Code"; --将产成品构建到总表中 TMP_ProcBom= SELECT * FROM :TMP_Product; --获取下级清单 TMP_Child= SELECT T0."ProductCode", T0."ProductName", T1."ChildCode" AS "ItemCode", T1."ChildName" AS "ItemName", T1."ChildQuantity" AS "Quantity", T0."BOM_Level"||'.'||RIGHT('000'||T1."ChildNum",3) AS "BOM_Level" FROM :TMP_Product T0 INNER JOIN :TMP_BOMDetail T1 ON T1."ItemCode"=T0."ItemCode"; --判断是否存在下级，存在则进行合并 SELECT COUNT(1) INTO CNT FROM :TMP_Child; WHILE :CNT>0 DO --合并到总表 TMP_ProcBom= SELECT * FROM :TMP_ProcBom UNION ALL SELECT * FROM :TMP_Child; --根据下级清单继续获取下下级清单 TMP_Child= SELECT T0."ProductCode", T0."ProductName", T1."ChildCode" AS "ItemCode", T1."ChildName" AS "ItemName", T1."ChildQuantity" AS "Quantity", T0."BOM_Level"||'.'||RIGHT('000'||T1."ChildNum",3) AS "BOM_Level" FROM :TMP_Child T0 INNER JOIN :TMP_BOMDetail T1 ON T1."ItemCode"=T0."ItemCode"; --获取下级记录数(当记录数>0时，循环合并和取下级操作) SELECT COUNT(1) INTO CNT FROM :TMP_Child; END WHILE; --返回结果 SELECT * FROM :TMP_ProcBom T0 ORDER BY T0."ProductCode",T0."BOM_Level"; END;