使用階層式方法擴展階層式資料表
AdventureWorks 行銷部門有 8 名員工。員工層級如下:
David, EmployeeID 6, 行銷經理。 David 管理三名行銷專員:
Sariya, EmployeeID 46
John, EmployeeID 271
Jill, EmployeeID 119
Sariya 管理行銷助理 Wanida (EmployeeID 269),John 管理行銷助理 Mary (EmployeeID 272)。
插入階層樹狀結構的根目錄
下列範例會將行銷經理 David 插入階層根目錄的資料表中。OrdLevel 資料行為計算資料行。因此,不是 INSERT 陳述式的一部分。第一筆記錄使用 GetRoot() 方法,將這個第一筆記錄擴展為階層的根目錄。
INSERT HumanResources.EmployeeOrg (OrgNode, EmployeeID, EmpName, Title) VALUES (hierarchyid::GetRoot(), 6, 'David', 'Marketing Manager') ; GO
執行下列節點以檢查資料表中的初始資料列:
SELECT OrgNode.ToString() AS Text_OrgNode, OrgNode, OrgLevel, EmployeeID, EmpName, Title FROM HumanResources.EmployeeOrg ;
以下為結果集:
Text_OrgNode OrgNode OrgLevel EmployeeID EmpName Title ------------ ------- -------- ---------- ------- ----------------- / Ox 0 6 David Marketing Manager
如同上一個課程,我們使用 ToString() 方法,將 hierarchyid 資料類型轉換為比較容易瞭解的格式。
插入從屬員工
David 管理 Sariya。若要插入 Sariya 的節點,您必須建立適合 hierarchyid 資料類型的 OrgNode 值。下列程式碼會建立 hierarchyid 資料類型的變數,並使用資料表的 OrgNode 根目錄值擴展。然後,搭配 GetDescendant() 方法使用該變數來插入從屬節點的資料列。GetDescendant 會使用兩個引數。檢閱下列引數值的選項:
如果父系為 NULL,GetDescendant 會傳回 NULL。
如果父系不是 NULL,而 child1 和 child2 都是 NULL,GetDescendant 則會傳回父系的一個子系。
如果父系和 child1 都不是 NULL,而 child2 是 NULL,GetDescendant 會傳回父系中大於 child1 的子系。
如果父系和 child2 不是 NULL,而 child1 是 NULL,GetDescendant 會傳回父系中小於 child2 的子系。
如果父系、child1 和 child2 都不是 NULL,GetDescendant 會傳回父系中大於 child1 且小於 child2 的子系。
下列程式碼使用根目錄父系的 (NULL, NULL) 引數,因為除了根目錄之外,資料表中還沒有任何資料列。執行下列程式碼以插入 Sariya:
DECLARE @Manager hierarchyid SELECT @Manager = hierarchyid::GetRoot() FROM HumanResources.EmployeeOrg ; INSERT HumanResources.EmployeeOrg (OrgNode, EmployeeID, EmpName, Title) VALUES (@Manager.GetDescendant(NULL, NULL), 46, 'Sariya', 'Marketing Specialist') ;
從第一個程序開始重複查詢,以查詢資料表並查看項目如何出現:
SELECT OrgNode.ToString() AS Text_OrgNode, OrgNode, OrgLevel, EmployeeID, EmpName, Title FROM HumanResources.EmployeeOrg ;
以下為結果集:
Text_OrgNode OrgNode OrgLevel EmployeeID EmpName Title ------------ ------- -------- ---------- ------- ----------------- / Ox 0 6 David Marketing Manager /1/ 0x58 1 46 Sariya Marketing Specialist
建立輸入新節點的程序
若要簡化輸入資料的方式,建立下列預存程序,將員工新增到 EmployeeOrg 資料表中。該程序會接受要新增之員工的輸入值。這包括新員工之主管的 EmployeeID、新員工的 EmployeeID 號碼,及其姓氏和職稱。此程序會使用 GetDescendant() 以及 GetAncestor() 方法。執行下列程式碼以建立程序:
CREATE PROC AddEmp(@mgrid int, @empid int, @e_name varchar(20), @title varchar(20)) AS BEGIN DECLARE @mOrgNode hierarchyid, @lc hierarchyid SELECT @mOrgNode = OrgNode FROM HumanResources.EmployeeOrg WHERE EmployeeID = @mgrid SET TRANSACTION ISOLATION LEVEL SERIALIZABLE BEGIN TRANSACTION SELECT @lc = max(OrgNode) FROM HumanResources.EmployeeOrg WHERE OrgNode.GetAncestor(1) =@mOrgNode ; INSERT HumanResources.EmployeeOrg (OrgNode, EmployeeID, EmpName, Title) VALUES(@mOrgNode.GetDescendant(@lc, NULL), @empid, @e_name, @title) COMMIT END ; GO
下列範例會加入 David 直接或間接管理的其餘 4 名員工。
EXEC AddEmp 6, 271, 'John', 'Marketing Specialist' ; EXEC AddEmp 6, 119, 'Jill', 'Marketing Specialist' ; EXEC AddEmp 46, 269, 'Wanida', 'Marketing Assistant' ; EXEC AddEmp 271, 272, 'Mary', 'Marketing Assistant' ;
再次執行下列查詢,檢查 EmployeeOrg 資料表中的資料列:
SELECT OrgNode.ToString() AS Text_OrgNode, OrgNode, OrgLevel, EmployeeID, EmpName, Title FROM HumanResources.EmployeeOrg ; GO
以下為結果集:
Text_OrgNode OrgNode OrgLevel EmployeeID EmpName Title ------------ ------- -------- ---------- ------- ----------------- / Ox 0 6 David Marketing Manager /1/ 0x58 1 46 Sariya Marketing Specialist /1/1/ 0x5AC0 2 269 Wanida Marketing Assistant /2/ 0x68 1 271 John Marketing Specialist /2/1/ 0x6AC0 2 272 Mary Marketing Assistant /3/ 0x78 1 119 Jill Marketing Specialist
資料表現在已經完全擴展到行銷組織了。