树操作

在开发数据库应用程序中,我们经常需要表示多层次关系的数据,譬如: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;

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值