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

 

 

 

转载于:https://www.cnblogs.com/Cwdf/archive/2010/05/30/1747715.html