hierarchyid データの操作

次の例のサンプル コードは、新しい子 EmployeeId 値を計算してキー違反を検出し、INS_EMP マーカーに戻って新しい行の EmployeeId 値を再計算します。

USE AdventureWorks2008R2;
GO

CREATE TABLE Org_T1
   (
    EmployeeId hierarchyid PRIMARY KEY,
    OrgLevel AS EmployeeId.GetLevel(),
    EmployeeName nvarchar(50) 
   ) ;
GO

CREATE INDEX Org_BreadthFirst ON Org_T1(OrgLevel, EmployeeId)
GO

CREATE PROCEDURE AddEmp(@mgrid hierarchyid, @EmpName nvarchar(50) ) 
AS
BEGIN
    DECLARE @last_child hierarchyid
INS_EMP: 
    SELECT @last_child = MAX(EmployeeId) FROM Org_T1 
    WHERE EmployeeId.GetAncestor(1) = @mgrid
INSERT Org_T1 (EmployeeId, EmployeeName)
SELECT @mgrid.GetDescendant(@last_child, NULL), @EmpName 
-- On error, return to INS_EMP to recompute @last_child
IF @@error <> 0 GOTO INS_EMP 
END ;
GO

Org_BreadthFirst インデックスによって、@last_child が範囲シークであるかどうかを判断できるようになります。アプリケーションでチェックできるその他のエラーの場合だけでなく、挿入後の重複キー違反は、同じ ID を持つ複数の従業員を追加しようとしていることを示します。したがって、@last_child を再計算する必要があります。次のコードは、シリアル化可能なトランザクションと幅優先のインデックスを使用して、新しいノード値を計算します。

CREATE TABLE Org_T2
    (
    EmployeeId hierarchyid PRIMARY KEY,
    LastChild hierarchyid, 
    EmployeeName nvarchar(50) 
    ) ;
GO

CREATE PROCEDURE AddEmp(@mgrid hierarchyid, @EmpName nvarchar(50)) 
AS
BEGIN
DECLARE @last_child hierarchyid
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION 

UPDATE Org_T2 
SET @last_child = LastChild = EmployeeId.GetDescendant(LastChild,NULL)
WHERE EmployeeId = @mgrid
INSERT Org_T2 (EmployeeId, EmployeeName) 
    VALUES(@last_child, @EmpName)
COMMIT
END ;

次のコードは、テーブルに 3 つの行を挿入して、その結果を返します。

INSERT Org_T2 (EmployeeId, EmployeeName) 
    VALUES(hierarchyid::GetRoot(), 'David') ;
GO
AddEmp 0x , 'Sariya'
GO
AddEmp 0x58 , 'Mary'
GO
SELECT * FROM Org_T2

以下に結果セットを示します。

EmployeeId LastChild EmployeeName
---------- --------- ------------
0x        0x58       David
0x58      0x5AC0     Sariya
0x5AC0    NULL       Mary

トップに戻る

ノードの先祖のリストの作成は、組織内での位置を表示するなどの一般的な操作です。これを実行するには、上で定義した HierarchyId_Operations クラスを使用して、テーブル値関数を使用するのが 1 つの方法です。

Transact-SQL の使用

CREATE FUNCTION ListAncestors (@node hierarchyid)
RETURNS TABLE (node hierarchyid)
AS
EXTERNAL NAME HierarchyId_Operations.HierarchyId_Operations.ListAncestors
GO

使用例

DECLARE @h hierarchyid
SELECT @h = OrgNode 
FROM HumanResources.EmployeeDemo  
WHERE LoginID = 'adventure-works\janice0' -- /1/1/5/2/

SELECT LoginID, OrgNode.ToString() AS LogicalNode
FROM HumanResources.EmployeeDemo AS ED
JOIN ListAncestors(@h) AS A 
   ON ED.OrgNode = A.Node
GO

上で定義した HierarchyId_Operations クラスを使用して、次の Transact-SQL 関数を作成し、階層内の 2 つのノードに関連する最下位の共通の先祖を見つけます。

CREATE FUNCTION CommonAncestor (@node1 hierarchyid, @node2 hierarchyid)
RETURNS hierarchyid
AS
EXTERNAL NAME HierarchyId_Operations.HierarchyId_Operations.CommonAncestor
GO

使用例

DECLARE @h1 hierarchyid, @h2 hierarchyid

SELECT @h1 = OrgNode 
FROM  HumanResources.EmployeeDemo 
WHERE LoginID = 'adventure-works\jossef0' -- Node is /1/1/3/

SELECT @h2 = OrgNode 
FROM HumanResources.EmployeeDemo  
WHERE LoginID = 'adventure-works\janice0' -- Node is /1/1/5/2/

SELECT OrgNode.ToString() AS LogicalNode, LoginID 
FROM HumanResources.EmployeeDemo  
WHERE OrgNode = dbo.CommonAncestor(@h1, @h2) ;

結果ノードは /1/1/

トップに戻る