ERP中递归获取物料清单（BOM）方法

　　物料清单（Bill of Material, BOM），是制造执行系统 (manufacturing execution system，MES）和企业资源计划系统（Enterprise Resource Planning，ERP）里面必不可少的一个表，这个表最大的特点就是由无数棵树组成的森林，而且树之间还有交叉（共通枝叶），数据量比较大，在数据库二维表中以递归方式存储，对这个表的检索和操作是系统中比较重要的一个组成部分。下面简单写写数据的检索，主要涉及Sql server2008和oracle11中的检索方式。

1、Sql server2008 中的两种检索模式：

1） 存储过程方式，递归有32层的限制，而且游标很麻烦，没有oracle的那种行变量，很罗嗦。

View Code

 1 ALTER PROCEDURE [DBO].[GETBOMTREE](
 2     @UPCD  CHAR(25),
 3     @LEVEL  INT
 4 ) AS
 5 BEGIN
 6     SET NOCOUNT ON;
 7     DECLARE @FACCD CHAR(12), @UPPERCD CHAR(25), 
 8     @SEQ INT,@LOWERCD CHAR(25),@NEWLEVEL INT;
 9     
10     IF @LEVEL = 0 
11     BEGIN
12         SET @LEVEL=1;
13     END
14     
15     DECLARE BOM_CURSOR CURSOR LOCAL FOR   --注意是LOCAL游标
16 SELECT FAC_CD,UPPER_ CD,USE_SEQ,ISNULL(LOWER_ CD,'')
17         FROM T_BOM
18         WHERE UPPER_CD = @UPCD ;
19     
20     OPEN BOM_CURSOR;
21 
22     FETCH NEXT FROM BOM_CURSOR 
23     INTO @FACCD,@UPPERCD,@SEQ,@LOWERCD ;
24 
25     --
26     IF @@FETCH_STATUS <>0
27     BEGIN
28         CLOSE BOM_CURSOR;
29         DEALLOCATE BOM_CURSOR;
30         
31         RETURN ;
32     END 
33     
34     --
35     WHILE @@FETCH_STATUS = 0
36     BEGIN
37         --插入数据进入临时表(实际为物理表，每次执行时删除该表数据，还要增加终端号或用户名区分多用户操作，这里省略)
38         INSERT INTO T_BOM_TMP(
39             FAC_CD,UPPER_CD,USE_SEQ,LOWER_CD,LEVEL
40         ) VALUES (
41             @FACCD,@UPPERCD,@SEQ,@LOWERCD,@LEVEL
42         );
43 
44         --调用本身
45         SET @NEWLEVEL=@LEVEL + 1;
46         EXEC DBO.GETBOMTREE @LOWERCD,@NEWLEVEL;        
47 
48         FETCH NEXT FROM BOM_CURSOR 
49             INTO @FACCD, @UPPERCD,@SEQ,@LOWERCD ;
50     END    
51     
52     CLOSE BOM_CURSOR;
53     DEALLOCATE BOM_CURSOR;
54 END

2) CTE方式，该方式无递归限制，并且代码简洁，是第一选择。

View Code

 1 WITH BOM_CTE AS
 2 
 3 (
 4 
 5     SELECT *,1 AS ILEVEL FROM T_BOM WHERE
 6 
 7       FAC_CD   = 'FAC01' AND    --工厂代码
 8 
 9       UPPER_CD = 'AAA'   --要查找的根节点
10 
11     UNION ALL
12 
13     SELECT P.*,D.ILEVEL + 1 AS ILEVEL
14 
15     FROM T_BOM AS P
16 
17        INNER JOIN BOM_CTE AS D ON
18 
19            P.FAC_CD    = D.FAC_CD   AND
20 
21            P.UPPER_CD = D.LOWER_CD
22 
23 )
24 
25 SELECT * FROM BOM_CTE

2、ORACLE11中的两种方式

1）存储过程，我只写包体的主要部分（由于没有环境，代码没有经过测试）

View Code

 1 --插入临时表数据
 2 
 3   PROCEDURE INS_BOM_TMP(V_ROW_BOM T_BOM_TMP%ROWTYPE) IS  BEGIN
 4     INSERT INTO T_BOM_TMP VALUES V_ROW_BOM;
 5   END INS_BOM_TMP;
 6 
 7  --主要递归过程
 8 
 9 PROCEDURE GET_BOM(V_FAC_CD T_BOM.FAC_CD%TYPE,
10 
11 V_UPPDER_CD T_BOM.UPPER_CD%TYPE,
12 
13 LEVEL NUMBER(10,0)) IS
14 
15 CURSOR V_BOM_CURSOR IS
16       SELECT * FROM T_BOM WHERE FAC_CD = V_FAC_CD AND UPPER_CD= V_UPPDER_CD;
17     V_CUR V_BOM_CURSOR%ROWTYPE;
18     V_ROW T_BOM_TMP%ROWTYPE;
19 BEGIN
20     FOR V_CUR IN V_BOM_CURSOR LOOP
21         V_ROW.FAC_CD           := V_CUR.FAC_CD;
22         V_ROW.UPPER_ITEM_CD    := V_CUR.UPPER_CD;
23         V_ROW.USE_SEQ          := V_CUR.USE_SEQ;
24 
25     V_ROW.LOWER_ITEM_CD    := V_CUR.LOWER_CD;
26         V_ROW.LEVEL         := LEVEL;
27         INS_BOM_TMP (V_ROW);   --插入临时表
28 
29 GET_BOM(V_FAC_CD, V_CUR.LOWER_CD, LEVEL+1);  --递归调用
30     END LOOP;
31 EXCEPTION
32     WHEN OTHERS THEN
33       --定义一个错误函数记录错误日志
34       WRITE_ERR_LOG(PACKAGE_ID,'GET_BOM', SQLERRM);
35 END GET_BOM;

　　个人很喜欢oracle的存储过程编写，变量声明方式很好，函数也很丰富，尤其行变量很方便。

2） ORACLE的递归语句（相当简洁）

View Code

SELECT FAC_CD,UPPER_CD,USE_SEQ,LOWER_CD,LEVEL  --LEVEL是关键字
FROM T_BOM 
WHERE LEVEL <= 3 AND FAC_CD='FAC01'
START WITH UPPER_CD='AAA'   --根节点
CONNECT BY PRIOR LOWER_CD=UPPER_CD --从根到叶
--CONNECT BY  LOWER_CD=PRIOR UPPER_CD --从叶到根
ORDER SIBLINGS BY USE_SEQ   --子节点内部排序

3、LINQ方式获取BOM(Linq to Sql，递归)

View Code

 1 void Main()
 2 
 3 {
 4 
 5     var query=GetBom("AAA");
 6 
 7      Console.WriteLine("Upper Code");
 8 
 9      query.ToList().ForEach(q=>Console.WriteLine("{0}",q.UPPER_CD));
10 
11 }
12 
13  
14 
15 public IEnumerable<T_BOM> GetBom(string UpperCD)
16 
17 {
18 
19     var query = from b in T_BOM
20 
21           where b.UPPER_CD  == UpperCD
22 
23           select b;
24 
25         
26 
27     return  query.ToList().Concat(query.ToList().SelectMany(t => GetBom(t.LOWER_CD)));   
28 
29 }

　　在我本地机上用该方法在从10万多条数据中获取321条的一棵树，用时2秒多（LinqPad中执行的结果），无论用自己写的存储过程或CTE方式，都只有0.2秒左右（Management studio中执行的结果）。另外，观察LINQ生成的SQL语句，发现数据重复获取，由于效率低下，我采用了EF中用存储过程方式，不过总有种“如鲠在喉”的感觉，希望哪位大师能告诉我EF下如何递归大数据量的问题，不胜感激。

补充：上面的代码我也是网上找的一段代码，由于假期有点空，就仔细看了看代码，稍微变更一下，去掉了重复获取数据，性能有很大提高，0.6秒多，代码如下

View Code

 1 void Main()
 2 {
 3     var query=GetBom("AAA");
 4      Console.WriteLine("Upper Code");
 5      query.ToList().ForEach(q=>Console.WriteLine("{0}",q.UPPER_CD));
 6 }
 7 
 8 public IEnumerable<T_BOM> GetBom(string UpperCD)
 9 {
10     var list = (from b in T_BOM
11           where b.UPPER_CD  == UpperCD
12           select b).ToList();        
13 
14     return  list.Concat(list.SelectMany(t => GetBom(t.LOWER_CD)));
15 }

　　以上代码性能有很大提高，但总归有一定的欠缺。不过技术是死的，设计是活的，可以扩充“深度”字段和“页标识”字段来减少代码的复杂度和获取数据的灵活度，比如叶子是已知的情况，就不再去递归检索，在Web项目中还可以通过AJAX展开枝叶。不过要注意，如果树有交叉，“深度”字段是无意义的。也有的设计采用触发器自动更新深度字段，该方式虽然比较好，但用ORM框架总是希望数据库无关的。当然，各种技术和设计的优缺点都有，仁者见仁，智者见智罢了。

4、附上Tree代码生成函数（winform程序，VB.net代码，非递归）

View Code

 1 Private Sub CreateBomTree(dtBom As DataTable, tnParent As Windows.Forms.TreeNode)
 2     Dim tnChild As System.Windows.Forms.TreeNode
 3     Dim iLevel As Int16 = 1         '层号
 4     For Each dr As DataRow In dtBom.Rows
 5         tnChild = New Windows.Forms.TreeNode
 6         tnChild.Name = dr("LOWER_CD").ToString().Trim()
 7         tnChild.Text = dr("USE_SEQ") & " " & dr("LOWER_DESC").ToString().Trim()
 8         If dr("LEVEL") = iLevel Then
 9             tnParent.Nodes.Add(tnChild)
10         ElseIf dr("LEVEL") > iLevel Then
11             iLevel = dr("LEVEL")
12             tnParent = tnParent.LastNode
13             tnParent.Nodes.Add(tnChild)
14         Else
15             For i As Int16 = dr("LEVEL") To iLevel - 1
16                 tnParent = tnParent.Parent
17             Next
18 
19             iLevel = dr("LEVEL")
20             tnParent.Nodes.Add(tnChild)
21         End If
22     Next
23 End Sub

 

转载于:https://www.cnblogs.com/Kylin98/archive/2012/10/06/2712928.html