SQL SERVER 2008 新数据库类型hierarchyid

 

SQL SERVER 2008 新数据库类型 hierarchyid

      

    SQL SERVER 2008 引入了新的 hierarchyid 数据类型 , 可以用它来做本地存储并且在树层次

结构中管理其位置 . 只用这个函数能简洁地表示层次结构 中的位置 . 该函数提供的一些内置的函

数方法可以操作和遍历层次结构 , 使得存储和查询分层数据更为容易 , 而不需要像那样通过

CTE 递归来获得 .

 

    该类型其实是一个 CLR 自定义数据类型依次打开 : 数据库 -> 系统数据库 ->master-> 可编程性

-> 类型 -> 系统数据类型 ->CLR 数据类型 ->hierarchyid, 可以看到该数据类型 .

   

    于 hierarchyid 有关的一些函数主要有 :

   

    GetAncestor ：取 得某一个级别的祖先

    GetDescendant ：取 得某一个级别的子代

    GetLevel ：取 得级别

    GetRoot ：取得根

    IsDescendantOf ：判 断某个节点是否为某个节点的子代

    Parse ：将字符串转换为 hierarchyid 。该字符串的格式通常都是 /1/ 这样的

    Read ： Read 从传入的 BinaryReader 读取 SqlHierarchyId 的二进制表示形式，并将

           SqlHierarchyId 对象 设置为该值。不能使用 Transact-SQL 调用 Read 。请改

           为使用 CAST 或 CONVERT 。

    GetReparentedValue ：可 以用来移动节点（或者子树）

    ToString ：将 hierarchyid 转换为字符串，与 parse 正好相反

    Write : 将 SqlHierarchyId 的二进制表示形式写出到传入的 BinaryWriter 中。无法通

           过使用 Transact-SQL 来调用 Write 。请改为使用 CAST 或 CONVERT 。

 

 

    以下就该新类型做一些演示

*/

 

    USE TESTDB

    GO

   

    CREATE TABLE TEST (

           [PHASE] hierarchyid NOT NULL,

           LVL AS [PHASE] . GetLevel (),

           USERID INT NOT NULL,

           USERNAME VARCHAR ( 50) NOT NULL

           )

   

    -- 接着插入一个根

   

    INSERT TEST ( [PHASE] , USERID , USERNAME )

    VALUES ( '/' , 1001, ' 东升 ' ) --'/' 被用来表示层次的根 , 会自动转换成二进制格式

   

   

    SELECT * FROM TEST

   

/*  结果

 

    PHASE  LVL    USERID     USERNAME

    ------ ------ ---------  ----------

    0x     0      1001       东升

 

    (1 行受影响 )

   

    查询后应该发现 '/' 被重新定义成 x 十六进制值 .

   

    使用斜杠字符来表示层次路径 , 一个表示的是根 , 用斜杠分隔的整数值来组成连续的层 次 .

   

   

    插入小弟

*/

    INSERT TEST ( [PHASE] , USERID , USERNAME )

    VALUES ( '/1/' , 1002, ' 土豆 ' )

   

    INSERT TEST ( [PHASE] , USERID , USERNAME )

    VALUES ( '/2/' , 1003, ' 红太狼 ' )

    -- 以上条数据时同级别的

   

   

    SELECT * FROM TEST

/*

    PHASE      LVL    USERID      USERNAME

    -------    ------ ---------  -----------

    0x         0      1001        东升

    0x58       1      1002        土豆

    0x68       1      1003        红太狼

 

    (3 行受影响 )

   

*/

   

   

    -- 同样可以使用 GetDescendant 方法来填充 [PHASE]

    DECLARE     @PARENTPHASE hierarchyid , @Child1 hierarchyid

    SELECT @PARENTPHASE = CONVERT ( hierarchyid , '/1/' )

   

    INSERT TEST ( [PHASE] , USERID , USERNAME )

    VALUES ( @PARENTPHASE . GetDescendant (NULL,NULL), 1004, ' 土豆小弟 1 ' )

   

    SELECT @Child1 = CAST ( '/1/1/' AS hierarchyid )

    INSERT TEST ( [PHASE] , USERID , USERNAME )

    VALUES ( @PARENTPHASE . GetDescendant ( @Child1 ,NULL), 1005, ' 土豆小弟 2 ' )

   

    SELECT [PHASE] . ToString () AS [PHASE] , LVL , USERID , USERNAME FROM TEST

    ORDER BY [PHASE]

   

/*

    PHASE      LVL    USERID      USERNAME

    ---------- ------ ---------  ---------

    /          0      1001        东升

    /1/        1      1002        土豆

    /1/1/      2      1004        土豆小弟 1

    /1/2/      2      1005        土豆小弟 2

    /2/        1      1003        红太狼

 

    (5 行受影响 )

 

    查询中的中 ToString 方法可以显示 hierarchyid 类型的字符串表示 , 而不是十六进制值 .

    该方法下面会经常用到 .

   

   

    下面演示一下上面提到的函数方法 ( 注意方法的调用要注意大小写 ):

   

   

1 GetAncestor: child.GetAncestor ( n ) 返回指定的祖先 .

       用于测试输出中的每个节点是否将 当前节点作为指定级别的祖先。

       如果传递的数字大于 GetLevel() ，则返回 NULL 。

       如果传递的是负数，则引发异常

 

*/

 

    SELECT PHASE . GetAncestor ( 2). ToString () AS Ancestor

           -- 把对应的二级祖先的层次显示为字符串

    , PHASE . ToString () as CURPHASE

    , USERNAME

    FROM TEST

    WHERE LVL >= 2 -- 去除级别小于的行           

 

/*

    Ancestor       CURPHASE   USERNAME

    -----------   ---------  ----------

    /              /1/1/      土豆小弟 1

    /              /1/2/      土豆小弟 2

 

    (2 行受影响 )

   

    注意 GetAncestor(0) 返回的是节点本身

 

 

2 GetDescendant: parent.GetDescendant ( child1 , child2 ) 返回子节点

    child1:NULL 或当 前节点的子节点的 hierarchyid 。

    child2:NULL 或当 前节点的子节点的 hierarchyid 。

 

    如果父级为 NULL ，则返回 NULL 。

    如果父级不为 NULL ，而 child1 和 child2 为 NULL ，则返回父级的子级。

    如果父级和 child1 不为 NULL ，而 child2 为 NULL ，则返回一个大于 child1 的父级的子级。

    如果父级和 child2 不为 NULL ，而 child1 为 NULL ，则返回一个小于 child2 的父级的子级。

    如果父级、 child1 和 child2 都不为 NULL ，则返回一个大于 child1 且小 于 child2 的父级的子级。

    如果 child1 不为 NULL 且不 是父级的子级，则引发异常。

    如果 child2 不为 NULL 且不 是父级的子级，则引发异常。

    如果 child1 >= child2 ，则引发异常。

*/

 

    -- 插入一行作为最低级别的后代节点 , 上面插入的时候就运用的这个特点

    -- 不含参数的 GetDescendant 方法 插入新行，以将新行节点指定为 /1/1/

    -- 上面执行过 , 此处不执行

    DECLARE     @PARENTPHASE hierarchyid

    SELECT @PARENTPHASE = CONVERT ( hierarchyid , '/1/' )

    INSERT TEST ( [PHASE] , USERID , USERNAME )

    VALUES ( @PARENTPHASE . GetDescendant (NULL,NULL), 1004, ' 土豆小弟 1 ' )

   

    -- 插入一行作为较高级别的后代节点

    -- 使用带有 Child1 参数 的 GetDescendant 方法插入新行

    -- 指定新行的节点将紧跟 '/1/1/' 节点，成为 /1/2/

    -- 上面执行过 , 此处不执行

    DECLARE     @PARENTPHASE hierarchyid , @Child1 hierarchyid

    SELECT @PARENTPHASE = CONVERT ( hierarchyid , '/1/' )

    SELECT @Child1 = CAST ( '/1/1/' AS hierarchyid )

    INSERT TEST ( [PHASE] , USERID , USERNAME )

    VALUES ( @PARENTPHASE . GetDescendant ( @Child1 ,NULL), 1005, ' 土豆小弟 2 ' )

   

    -- 在两个现有节点之间插入一行

    -- 同时使用 child1 参数 和 child2 参数指定新行的节点将成为节点 /1/1.1/

    DECLARE     @PARENTPHASE hierarchyid , @Child1 hierarchyid , @Child2 hierarchyid

    SELECT @PARENTPHASE = CONVERT ( hierarchyid , '/1/' )

    SELECT @Child1 = CAST ( '/1/1/' AS hierarchyid )

    SELECT @Child2 = CAST ( '/1/2/' AS hierarchyid )

    INSERT TEST ( [PHASE] , USERID , USERNAME )

    VALUES ( @PARENTPHASE . GetDescendant ( @Child1 , @Child2 ), 1006, ' 土豆小弟 ' )

   

    SELECT [PHASE] . ToString () AS [PHASE] , lvl , userid , username

    FROM   test ORDER BY [PHASE]

   

    /*

    PHASE      lvl    userid      username

    ------     ------ ---------- ------------

    /          0      1001        东升

    /1/        1      1002        土豆

    /1/1.1/    2      1006        土豆小弟 3

    /1/1/      2      1004        土豆小弟 1

    /1/2/      2      1005        土豆小弟 2

    /2/        1      1003        红太狼

 

    (6 行受影响 )

   

    节点 /1/1.1/ 高于节点 /1/1/ 低于节点 /1/2/, 但是属于同一级别

   

   

3 GetLevel (): node.GetLevel () 返回一个表示节点在树中的深度的 整数。

*/

 

    -- 将层次结构级别返回为列 , 本例的测试表建立的计算列用的就是这个特性 .

   

    SELECT PHASE . GetLevel () AS lvl1 , LVL FROM TEST

    /*

    lvl1   LVL

    ------ ------

    0      0

    1      1

    1      1

    2      2

    2      2

    2      2

 

    (6 行受影响 )

    */

   

    -- 返回层次结构级别的所有成员

   

    SELECT * FROM TEST WHERE PHASE . GetLevel ()= 1

   

    /*

    PHASE  LVL    USERID      USERNAME

    ------ ------ ---------- -----------

    0x58   1      1002        土豆

    0x68   1      1003        红太狼

 

    (2 行受影响 )

   

    同样可以用 PHASE.GetLevel()=0 返回根节点

   

   

   

   

4 IsDescendantOf ： child.IsDescendantOf(parent) 判断某个节点是否为某个节点的子代

*/

    DECLARE @parent hierarchyid

    SELECT @parent = PHASE FROM TEST WHERE USERNAME = ' 土豆 '

    SELECT * FROM TEST

    WHERE PHASE . IsDescendantOf ( @parent )= 1

   

    /*

    PHASE  LVL    USERID      USERNAME

    ------ ------ -----------   -----------

    0x58   1      1002        土豆

    0x5AC0 2      1004        土豆小弟 1

    0x5B40 2      1005        土豆小弟 2

    0x5B16 2      1006        土豆小弟 3

 

    (4 行受影响 )

   

    父级被视为其本身的后代 , 所以土豆的子代中包含土豆

 

 

5 GetRoot: 返回层次结构树的根节点 , 是一个静态方法。

*/

 

    -- 获得根节点

   

    SELECT PHASE . ToString () AS ROOT , *

    FROM TEST

    WHERE PHASE = hierarchyid :: GetRoot ()

 

 

    /*

    ROOT       PHASE      LVL    USERID      USERNAME

    -------    -------    -----  ---------  ----------

    /          0x         0      1001        东升

 

    (1 行受影响 )

   

 

6 Parse: 将 hierarchyid 的规范字符串表示形式转换为 hierarchyid 值。当发

         生从字符串类型到 hierarchyid 的转换时，将隐式调用 Parse 。作用

         与 ToString 相反。 Parse() 是静态方法。

  ToString: 将 hierarchyid 转换为字符串，与 parse 正好相反

*/

 

 

    DECLARE @String AS nvarchar ( 100), @hierarchyid AS hierarchyid

    SET @String = '/1/1/'

    SET @hierarchyid = 0x5AC0

 

    SELECT hierarchyid :: Parse ( @String ) AS hierarchyidvalue ,

           @hierarchyid . ToString () AS Stringvalue

          

    SELECT CONVERT ( hierarchyid , @String )

           , CONVERT ( nvarchar ( 100), @hierarchyid )

   

   

    /*

    hierarchyidvalue  Stringvalue

    ----------------  -------------

    0x5AC0            /1/1/

 

    (1 行受影响 )

   

    可以看到结果二者的作用刚好是相 反的 . 其实测试后他们可以发现 CONVERT 也

    可以实现他们的效果 . 如果 Parse 收到的值不是 hierarchyid 的有效字符

    串表示形式，则会引发异常 , 例如尾部有空格

   

    SELECT hierarchyid::Parse('/1/1 ')

   

    ----------------------------------------------------------------------------------

    消息 6522 ，级别 16 ，状态 2 ，第 1 行

    在执行用户定义例程或聚合 "hierarchyid" 期间出现 .NET Framework 错误 :

    Microsoft.SqlServer.Types.HierarchyIdException: 24001: SqlHierarchyId.Parse 失败

    ，因为输入字符串 '/1/1 ' 不是 SqlHierarchyId 节点 的有效字符串表示形式。

    Microsoft.SqlServer.Types.HierarchyIdException:

    在 Microsoft.SqlServer.Types.SqlHierarchyId.Parse(SqlString input)

 

 

 

7 GetReparentedValue : 节点或者是子树的移动

   

   

    1) 节点的移动

*/

 

    -- 查询一下前后的变化

 

    DECLARE @ActionUser hierarchyid , @OldParent hierarchyid , @NewParent hierarchyid

    SELECT @ActionUser = PHASE FROM TEST

      WHERE USERNAME = ' 土豆小弟 3 ' --/1/1.1/

    SELECT @OldParent = PHASE FROM TEST

      WHERE USERNAME = ' 土豆 '   --  /1/1/

    SELECT @NewParent = PHASE FROM TEST

      WHERE USERNAME = ' 东升 '   --  /

 

    SELECT PHASE . ToString () AS Current_PHASE_AS_Text ,

    ( @ActionUser . GetReparentedValue ( @OldParent , @NewParent ) ). ToString () AS Proposed_PHASE_AS_Text ,

    PHASE AS Current_PHASE ,

    @ActionUser . GetReparentedValue ( @OldParent , @NewParent ) AS Proposed_PHASE ,

    LVL ,

    USERID ,

    USERNAME

    FROM TEST

    WHERE PHASE = @ActionUser ;

 

/*

    Current_PHASE_AS_Text    Proposed_PHASE_AS_Text   Current_PHASE Proposed_PHASE    LVL     USERID      USERNAME

    ----------------------   -----------------------  --------------    ---------------    -------    -----------   ----------

    /1/1.1/                  /1.1/                0x5B16        0x62C0        2      1006       土豆小弟 3

 

    (1 行受影响 )

   

   

    接着上面的代码进行更新

*/

 

    UPDATE TEST

    SET PHASE = @ActionUser . GetReparentedValue ( @OldParent , @NewParent )

    WHERE PHASE = @ActionUser

 

 

    SELECT PHASE . ToString () AS PHASE ,

    LVL , USERID , USERNAME

    FROM TEST

    WHERE USERNAME = ' 土豆小弟 3 ' ; -- /1.1/

 

/*

    PHASE  LVL    USERID      USERNAME

    ------ ------ ---------- ----------

    /1.1/  1      1006        土豆小弟 3

 

    (1 行受影响 )

   

   

 

 

2) 子树的移动

*/

 

    -- 把土豆这棵树移动到红太郎下面

    DECLARE @nold hierarchyid , @nnew hierarchyid

           , @oldP nvarchar ( 256)= ' 土豆 ' , @newP nvarchar ( 256)= ' 红太狼 '

 

 

    SELECT @nold = PHASE FROM TEST WHERE USERNAME = @oldP ;

 

 

           SELECT @nnew = PHASE FROM TEST WHERE USERNAME = @newP ;

          

           SELECT @nnew = @nnew . GetDescendant ( max ( PHASE ), NULL)

           FROM TEST WHERE PHASE . GetAncestor ( 1)= @nnew ;

 

           UPDATE TEST  

           SET PHASE = PHASE . GetReparentedValue ( @nold , @nnew )

           WHERE PHASE . IsDescendantOf ( @nold ) = 1 ;

 

 

 

 

    SELECT PHASE . ToString () AS PHASE , LVL , USERID , USERNAME FROM TEST

    ORDER BY PHASE

   

/* 更新后的结果

    PHASE         LVL    USERID      USERNAME

    ------------  ------ -----------   ----------

    /             0      1001        东升

    /1.1/         1      1006       土豆小弟 3

    /2/           1      1003       红太狼

    /2/1/         2      1002       土豆

    /2/1/1/       3      1004       土豆小弟 1

    /2/1/2/       3      1005       土豆小弟 2

 

    (6 行受影响 )

 

 

8 Read,Write  这 2 个方法不能使用 Transact-SQL 调用 , 可以使用 CAST 或 CONVERT 代替。

*/