~楓花雪岳~: [SQL] HierarchyID

星期二, 7月 04, 2023

[SQL] HierarchyID - CRUD

接續 [SQL] HierarchyID - 資料型態，該篇以 HierarchyID CRUD 為主要內容並記錄相關函數，以官方文章 - 在階層式資料表中建立與管理資料為主要參考

環境建置

Table Schema 以最單純方式來建立，直接以 HierarchyID 當成 PK，方便記錄 CRUD

CREATE TABLE tblHID  
( 
	HID HierarchyID PRIMARY KEY CLUSTERED NOT NULL ,  
	EmpID int UNIQUE NOT NULL ,  
	EmpName varchar(20) NOT NULL ,  
	Title nvarchar(20) NULL 
) ;

文章內容統一以該語法來進行查詢操作後結果，就不在反覆寫出

SELECT 
	HID , 
	HID.ToString() AS HPath ,   
	HID.GetLevel() AS HLevel ,
	EmpID, 
	CAST(REPLICATE(SPACE(1) , HID.GetLevel() * 10) + EmpName as varchar(4000)) AS NameTree ,
	Title   
FROM tblHID ;

insert

GetRoot()：透過 hierarchyid::GetRoot() 來取得根節點

INSERT tblHID (HID, EmpID, EmpName, Title)  
	VALUES (hierarchyid::GetRoot(), 1, 'David', N'行銷經理') ;

GetDescendant()：傳回父系子節點，使用語法為

parent.GetDescendant ( child1 , child2 )

語法中 parent、child1、child2 三參數使用重點

如果 parent 為 NULL，則會傳回 NULL。
如果 parent 不是 NULL，而 child1 和 child2 都是 NULL，則會傳回 parent 的一個子節點。
如果 parent 和 child1 都不是 NULL，而 child2 是 NULL，則會傳回 parent 中大於 child1 的子節點。
如果 parent 和 child2 不是 NULL，而 child1 是 NULL，則會傳回 parent 中小於 child2 的子節點。
如果 parent、child1 和 child2 都不是 NULL，則會傳回 parent 中大於 child1 且小於 child2 的子節點。
如果 child1 不是 NULL，也不是 parent 的子節點，則會引發例外狀況。
如果 child2 不是 NULL，也不是 parent 的子節點，則會引發例外狀況。
如果 child1 >= child2，則會引發例外狀況。

DECLARE @Manager hierarchyid = hierarchyid::GetRoot()  
INSERT tblHID (HID, EmpID, EmpName, Title)  
	VALUES (@Manager.GetDescendant(NULL, NULL), 2, 'Sariya', N'行銷專員') ;

上述語法只能執行一次，當第二次執行時，會拋出違反 PK Exception，因為 GetDescendant 相同參數 (parent、child1、child2) 會得到相同結果

為方便後續建立子節點資料，建立該 Store Procedure 來反覆 insert 資料

CREATE OR ALTER PROCEDURE AddEmp
(
	@mgrID int null, 
	@empID int, 
	@empName varchar(20), 
	@title nvarchar(20))   
AS   
BEGIN  

	DECLARE @mgrHID hierarchyid, @lastChildHID hierarchyid , @hID hierarchyid

	IF @mgrID IS NULL
		SET @hID = HIERARCHYID::GetRoot();
	ELSE
		BEGIN

			SET @mgrHID = (SELECT HID FROM tblHID WHERE EmpID = @mgrID);

			-- 取得該父節點下最大子節點
			SET @lastChildHID =
			(
				SELECT MAX(HID)   
				FROM tblHID   
				-- HID.GetAncestor(1)：該節點父節點
				WHERE HID.GetAncestor(1) = @mgrHID
			);

			SET @hID = @mgrHID.GetDescendant(@lastChildHID, NULL);
		END
	
	INSERT tblHID (HID, EmpID, EmpName, Title)  
		VALUES(@hID, @empID, @empName, @title)
END ;  
GO

EXEC AddEmp 1, 3, 'John', N'行銷專員' ;  
EXEC AddEmp 1, 4, 'Jill', N'行銷專員' ;  
EXEC AddEmp 2, 5, 'Wanida', N'行銷助理' ;  
EXEC AddEmp 3, 6, 'Mary', N'行銷助理' ;
GO

insert 後結果

update

GetReparentedValue：該 node 從 oldParent 移至 newParent 的 HierarchyID

使用語法為

node.GetReparentedValue(oldParent, newParent)

實際執行

DECLARE @node hierarchyid , @oldParent hierarchyid, @newParent hierarchyid 
-- Wanida：待移動節點
SELECT @node = HID FROM tblHID WHERE EmpID = 5 ; 
-- Sariya：舊父節點
SELECT @oldParent = HID FROM tblHID WHERE EmpID = 2 ;
-- Jill：新父節點
SELECT @newParent = HID FROM tblHID WHERE EmpID = 4 ; 

UPDATE tblHID
SET HID = @node.GetReparentedValue(@oldParent, @newParent)   
WHERE HID = @node ;  
GO