物料清单（Bill of Material, BOM），是制造执行系统 (manufacturing execution system，MES）和企业资源计划系统（Enterprise Resource Planning，ERP）里面必不可少的一个表，这个表最大的特点就是由无数棵树组成的森林，而且树之间还有交叉（共通枝叶），数据量比较大，在数据库二维表中以递归方式存储，对这个表的检索和操作是系统中比较重要的一个组成部分。下面简单写写数据的检索，主要涉及Sql server和oracle11中的检索方式。
1、Sql server中的两种检索模式：
1）存储过程方式，递归有32层的限制，而且游标很麻烦，没有oracle的那种行变量，很罗嗦。
View Code
1 ALTER PROCEDURE [DBO].[GETBOMTREE]( 2@UPCD CHAR(25), 3@LEVEL INT 4 ) AS 5 BEGIN 6SET NOCOUNT ON; 7DECLARE @FACCD CHAR(12), @UPPERCD CHAR(25), 8@SEQ INT,@LOWERCD CHAR(25),@NEWLEVEL INT; 910IF @LEVEL = 0 11BEGIN12 SET @LEVEL=1;13END1415DECLARE BOM_CURSOR CURSOR LOCAL FOR --注意是LOCAL游标16 SELECT FAC_CD,UPPER_ CD,USE_SEQ,ISNULL(LOWER_ CD,'')17 FROM T_BOM18 WHERE UPPER_CD = @UPCD ;1920OPEN BOM_CURSOR;21 22FETCH NEXT FROM BOM_CURSOR 23INTO @FACCD,@UPPERCD,@SEQ,@LOWERCD ;24 25--26IF @@FETCH_STATUS <>027BEGIN28 CLOSE BOM_CURSOR;29 DEALLOCATE BOM_CURSOR;30 31 RETURN ;32END 3334--35WHILE @@FETCH_STATUS = 036BEGIN37 --插入数据进入临时表(实际为物理表，每次执行时删除该表数据，还要增加终端号或用户名区分多用户操作，这里省略)38 INSERT INTO T_BOM_TMP(39 FAC_CD,UPPER_CD,USE_SEQ,LOWER_CD,LEVEL40 ) VALUES (41 @FACCD,@UPPERCD,@SEQ,@LOWERCD,@LEVEL42 );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 ;50END 5152CLOSE BOM_CURSOR;53DEALLOCATE BOM_CURSOR;54 END
2) CTE方式，该方式无递归限制，并且代码简洁，是第一选择。
View Code
1 WITH BOM_CTE AS 2 3 ( 4 5SELECT *,1 AS ILEVEL FROM T_BOM WHERE 6 7 FAC_CD = 'FAC01' AND --工厂代码 8 9 UPPER_CD = 'AAA' --要查找的根节点10 11UNION ALL12 13SELECT P.*,D.ILEVEL + 1 AS ILEVEL14 15FROM T_BOM AS P16 17 INNER JOIN BOM_CTE AS D ON18 19 P.FAC_CD = D.FAC_CD AND20 21 P.UPPER_CD = D.LOWER_CD22 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 4INSERT 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)) IS14 15 CURSOR V_BOM_CURSOR IS16 SELECT * FROM T_BOM WHERE FAC_CD = V_FAC_CD AND UPPER_CD= V_UPPDER_CD;17V_CUR V_BOM_CURSOR%ROWTYPE;18V_ROW T_BOM_TMP%ROWTYPE;19 BEGIN20FOR V_CUR IN V_BOM_CURSOR LOOP21 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 25V_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); --递归调用30END LOOP;31 EXCEPTION32WHEN OTHERS THEN33 --定义一个错误函数记录错误日志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 5var query=GetBom("AAA"); 6 7Console.WriteLine("Upper Code"); 8 9query.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 19var query = from b in T_BOM20 21 where b.UPPER_CD == UpperCD22 23 select b;24 25 26 27return 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 { 3var query=GetBom("AAA"); 4Console.WriteLine("Upper Code"); 5query.ToList().ForEach(q=>Console.WriteLine("{0}",q.UPPER_CD)); 6 } 7 8 public IEnumerable<T_BOM> GetBom(string UpperCD) 9 {10var list = (from b in T_BOM11 where b.UPPER_CD == UpperCD12 select b).ToList(); 13 14return list.Concat(list.SelectMany(t => GetBom(t.LOWER_CD)));15 }
以上代码性能有很大提高，但总归有一定的欠缺。不过技术是死的，设计是活的，可以扩充“深度”字段和“页标识”字段来减少代码的复杂度和获取数据的灵活度，比如叶子是已知的情况，就不再去递归检索，在Web项目中还可以通过AJAX展开枝叶。不过要注意，如果树有交叉，“深度”字段是无意义的。也有的设计采用触发器自动更新深度字段，该方式虽然比较好，但用ORM框架总是希望数据库无关的。当然，各种技术和设计的优缺点都有，仁者见仁，智者见智罢了。
4、附上Tree代码生成函数（winform程序，代码，非递归）
View Code
1 Private Sub CreateBomTree(dtBom As DataTable, tnParent As Windows.Forms.TreeNode) 2Dim tnChild As System.Windows.Forms.TreeNode 3Dim iLevel As Int16 = 1 '层号 4For 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 Then11 iLevel = dr("LEVEL")12 tnParent = tnParent.LastNode13 tnParent.Nodes.Add(tnChild)14 Else15 For i As Int16 = dr("LEVEL") To iLevel - 116 tnParent = tnParent.Parent17 Next18 19 iLevel = dr("LEVEL")20 tnParent.Nodes.Add(tnChild)21 End If22Next23 End Sub
