树操作

在开发数据库应用程序中，我们经常需要表示多层次关系的数据，譬如：MRP中的BOM，多级别的组织结构，多级的角色关系…… 在数据库中描述这种关系时，我们通常使用父节点指向的方式来设计表结构，譬如对部门的表结构定义如下：

　　CREATE TABLE TDepartment
　　(
　　　　ParentNo varchar(25),    /* 上级部门编号，如果为NULL则表示当前记录为顶级部门。 */
　　　　DepartmentNo varchar(25) PRIMARY KEY CLUSTERED, /* 当前部门编号，主关键字段。 */
　　　　Name  nvarchar(100) NOT NULL,   /* 部门名称。 */
　　　　NameEx  nvarchar(100),    /* 扩展名称。 */
　　　　Remark  nvarchar(500)    /* 备　　注。 */
　　)

　　功能需求：获取指定部门编号及其所属的所有或指定层次的下级部门的记录集。

　　在以往的开发中，我们通常是在客户端或者中间层来处理以上需求的，一般是通过编程语言（VB、Delphi、Java/.NET）来处理从数据库返回的记录集，这种方式通常需要从数据库获得整个表的记录，因为我们不能确定给定主键所具有的哪些下级记录，然后再在编程代码中使用递归的方式来获取相关的下级记录。这种使用递归的方式，通常难以避免数据中非法记录所引起的死循环问题（当然，你也可以处理这种问题，只是有些麻烦，而且通常也不够优美）。那么，能不能在数据库中就处理好这个问题呢，这样既能减少不必要的数据传输，又能避免使用递归所带来的负面影响，最好还能自动屏蔽掉非法数据指向的问题（当前记录的父指针字段指向其某个下级记录）。
　　呵呵，卖弄完毕，亮出匕首（代码，Transact-SQL/MS-SQLServer 2000）：

/* Copyright all(c) 2004 ZhongFeng, http://blog.csdn.net/SW515 */
CREATE PROCEDURE dbo.PSelectDepartmentHierarchy
　　@DepartmentNo    varchar(25),
　　@Depth           int  = -1
AS
　　SET NOCOUNT ON

　　DECLARE @TStack TABLE
　　(
　　　　Indicator varchar(50)
　　)

　　DECLARE @Index int
　　SET @Index = 0

　　INSERT INTO @TStack
　　　　SELECT DepartmentNo
　　　　FROM dbo.TDepartment
　　　　WHERE DepartmentNo LIKE @DepartmentNo

　　WHILE @@ROWCOUNT > 0 AND (@Index < @Depth OR @Depth < 0)
　　BEGIN
　　　　SET @Index = @Index + 1

　　　　INSERT INTO @TStack
　　　　　　SELECT DepartmentNo
　　　　　　FROM dbo.TDepartment
　　　　　　WHERE
　　　　　　　　ParentNo IN (SELECT Indicator FROM @TStack) AND
　　　　　　　　DepartmentNo NOT IN (SELECT Indicator FROM @TStack)
　　END

　　SELECT dbo.TDepartment.*
　　FROM @TStack AS t
　　　　INNER JOIN dbo.TDepartment ON
　　　　　　t.Indicator = dbo.TDepartment.DepartmentNo
GO

 

PS：

如果使用Oracle，则不必这么复杂。Oracle提供递归查询功能，如上面的需求，仅需要一句SQL语句即可轻松实现：

SELECT ParentNo, DepartmentNo, Name, NameEx, Remark
FROM TDepartment
START WITH DepartmentNo=指定的部门编号
CONNECT BY ParentNo = DepartmentNo;