在开发数据库应用程序中，我们经常需要表示多层次关系的数据，譬如：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
OK，打完收工。短小精悍！！！
你可以使用或部分引用该段代码，不过必须保留以上版权注释部分内容，并注释代码来源，否则将保留追究侵权的权利！
如果你发现有BUG请来信与我联系(SW515@21cn.com)，谢谢合作。
发表于 @ 2004年08月26日 17:14:00|评论(loading...)|编辑