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十六進制值.

    使用斜杠字元來表示層次路徑,一個表示的是根,用斜杠分隔的整數值來組成連續的層次.

    插入小弟

    VALUES('/1/',1002,'洋芋')

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

    --以上條資料時同級别的

/*

    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/')

    VALUES(@PARENTPHASE.GetDescendant(NULL,NULL),1004,'洋芋小弟1')

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

    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

    --插入一行作為較進階别的後代節點

    --使用帶有Child1 參數的GetDescendant 方法插入新行

    --指定新行的節點将緊跟'/1/1/'節點，成為/1/2/

    --在兩個現有節點之間插入一行

    --同時使用child1 參數和child2 參數指定新行的節點将成為節點/1/1.1/

    DECLARE    @PARENTPHASE hierarchyid, @Child1 hierarchyid,@Child2hierarchyid

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

    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

    2      2

    */

    --傳回層次結構級别的所有成員

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

    PHASE LVL    USERID      USERNAME

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

    0x58   1      1002        洋芋

    0x68   1      1003        紅太狼

    同樣可以用PHASE.GetLevel()=0傳回根節點

4 IsDescendantOf ：child.IsDescendantOf(parent)判斷某個節點是否為某個節點的子代

    DECLARE @parent hierarchyid

    SELECT @parent=PHASE FROM TEST WHERE USERNAME='洋芋'

    WHERE PHASE.IsDescendantOf ( @parent )=1

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

    0x5AC0 2      1004        洋芋小弟1

    0x5B40 2      1005        洋芋小弟2

    0x5B16 2      1006        洋芋小弟3

    (4 行受影響)

    父級被視為其本身的後代,是以洋芋的子代中包含洋芋

5 GetRoot: 傳回層次結構樹的根節點,是一個靜态方法。

    --獲得根節點

    SELECT PHASE.ToString() AS ROOT, *

    WHERE PHASE = hierarchyid::GetRoot()

    ROOT       PHASE     LVL    USERID      USERNAME

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

    /          0x         0      1001        東升

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/

    可以看到結果二者的作用剛好是相反的.其實測試後他們可以發現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, @NewParenthierarchyid

    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() ASProposed_PHASE_AS_Text,

    PHASE AS Current_PHASE,

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

    LVL,

    USERID,

    USERNAME

    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

    接着上面的代碼進行更新

    UPDATE TEST

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

    WHERE PHASE = @ActionUser

    SELECT PHASE.ToString() AS PHASE,

    LVL,USERID,USERNAME

    WHERE USERNAME = '洋芋小弟3' ; -- /1.1/

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

    /1.1/ 1      1006        洋芋小弟3

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

8 Read,Write  這2個方法不能使用 Transact-SQL 調用,可以使用 CAST 或 CONVERT代替。

本文轉自溫景良(Jason)部落格園部落格，原文連結：http://www.cnblogs.com/wenjl520/archive/2010/06/01/1748811.html，如需轉載請自行聯系原作者