开发者社区> 科技小毛> 正文

SQL SERVER 2008 新数据库类型hierarchyid

简介:
+关注继续查看

/*

--2010-05-27(东升)

 

 

 

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代替。

*/

原文地址:http://blog.csdn.net/ldslove/archive/2010/05/27/5628007.aspx

 

 

本文转自温景良(Jason)博客园博客,原文链接:http://www.cnblogs.com/wenjl520/archive/2010/06/01/1748811.html,如需转载请自行联系原作者

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
SQL SERVER的数据类型
  数据类弄是数据的一种属性,表示数据所表示信息的类型。任何一种计算机语言都定义了自己的数据类型。当然,不同的程序语言都具有不同的特点,所定义的数据类型的各类和名称都或多或少有些不同。SQLServer 提供了 25 种数据类型:   ·Binary [(n)]   ·Varbinary [(n...
631 0
阿里云服务器端口号设置
阿里云服务器初级使用者可能面临的问题之一. 使用tomcat或者其他服务器软件设置端口号后,比如 一些不是默认的, mysql的 3306, mssql的1433,有时候打不开网页, 原因是没有在ecs安全组去设置这个端口号. 解决: 点击ecs下网络和安全下的安全组 在弹出的安全组中,如果没有就新建安全组,然后点击配置规则 最后如上图点击添加...或快速创建.   have fun!  将编程看作是一门艺术,而不单单是个技术。
18646 0
阿里云服务器怎么设置密码?怎么停机?怎么重启服务器?
如果在创建实例时没有设置密码,或者密码丢失,您可以在控制台上重新设置实例的登录密码。本文仅描述如何在 ECS 管理控制台上修改实例登录密码。
20370 0
SQL Server 2008数据类型
在创建表时,必须为表中的每列指派一种数据类型。本节将介绍SQL Server中最常用的一些数据类型。即使创建自定义数据类型,它也必须基于一种标准的SQL Server数据类型。例如,可以使用如下语法创建一种自定义数据类型(Address),但要注意,它基于SQL Server标准的varchar数据类型。
738 0
+关注
580
文章
0
问答
文章排行榜
最热
最新
相关电子书
更多
JS零基础入门教程(上册)
立即下载
性能优化方法论
立即下载
手把手学习日志服务SLS,云启实验室实战指南
立即下载