Auffüllen einer hierarchischen Tabelle mit hierarchischen Methoden
AdventureWorks hat 8 Mitarbeiter, die in der Marketingabteilung arbeiten. Die Angestelltenhierarchie sieht ungefähr wie folgt aus:
David, EmployeeID 6, ist der Marketing-Manager. Drei Marketingspezialisten berichten David:
Sariya, EmployeeID 46
John, EmployeeID 271
Jill, EmployeeID 119
Marketingassistent Wanida (EmployeeID 269) berichtet Sariya, und Marketingassistent Mary (EmployeeID 272) berichtet John.
So fügen Sie den Stamm in die Hierarchiestruktur ein
Das folgende Beispiel fügt den Marketing-Manager David als Stamm der Hierarchie in die Tabelle ein. Die Spalte OrdLevel ist eine berechnete Spalte. Sie ist daher kein Teil der INSERT-Anweisung. Der erste Datensatz verwendet die Methode GetRoot(), um diesem ersten Datensatz als Stamm der Hierarchie zu füllen.
INSERT HumanResources.EmployeeOrg (OrgNode, EmployeeID, EmpName, Title) VALUES (hierarchyid::GetRoot(), 6, 'David', 'Marketing Manager') ; GO
Führen Sie den folgenden Code aus, um die Anfangszeile in der Tabelle zu untersuchen:
SELECT OrgNode.ToString() AS Text_OrgNode, OrgNode, OrgLevel, EmployeeID, EmpName, Title FROM HumanResources.EmployeeOrg ;
Dies ist das Resultset.
Text_OrgNode OrgNode OrgLevel EmployeeID EmpName Title ------------ ------- -------- ---------- ------- ----------------- / Ox 0 6 David Marketing Manager
Wie in der vorigen Lektion verwenden wir die Methode ToString(), um den hierarchyid-Datentyp in ein leichter verständliches Format zu konvertieren.
So fügen Sie einen unterstellten Mitarbeiter ein
Sariya berichtet David. Um Sariyas Knoten einzufügen, müssen Sie einen entsprechenden OrgNode-Wert vom Datentyp hierarchyid erstellen. Das folgende Beispiel erstellt eine Variable des Datentyps hierarchyid und füllt sie mit dem OrgNode-Stammwert der Tabelle. Diese Variable wird zusammen mit der Methode GetDescendant() verwendet, um eine Zeile einzufügen, die den untergeordneten Knoten darstellt. GetDescendant übernimmt zwei Argumente. Für die Argumentwerte gelten die folgenden Optionen:
Ist parent NULL, gibt GetDescendant NULL zurück.
Ist parent nicht NULL und sind sowohl child1 als auch child2 NULL, dann gibt GetDescendant einen parent untergeordneten Knoten zurück.
Sind parent und child1 nicht NULL und ist child2 NULL, dann gibt GetDescendant einen parent untergeordneten Knoten zurück, der größer als child1 ist.
Sind parent und child2 nicht NULL und ist child1 NULL, dann gibt GetDescendant einen parent untergeordneten Knoten zurück, der kleiner als child2 ist.
Sind parent, child1 und child2 alle nicht NULL, dann gibt GetDescendant einen parent untergeordneten Knoten zurück, der größer als child1 und kleiner als child2 ist.
Der folgende Code verwendet die Argumente (NULL, NULL) des Stammknotens, da außer diesem Stammknoten noch keine Zeilen in der Tabelle vorhanden sind. Führen Sie den folgenden Code aus, um Sariya einzufügen:
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') ;
Wiederholen Sie die Abfrage der ersten Prozedur, um die Tabelle abzufragen und zu sehen, wie die Einträge angezeigt werden:
SELECT OrgNode.ToString() AS Text_OrgNode, OrgNode, OrgLevel, EmployeeID, EmpName, Title FROM HumanResources.EmployeeOrg ;
Dies ist das Resultset.
Text_OrgNode OrgNode OrgLevel EmployeeID EmpName Title ------------ ------- -------- ---------- ------- ----------------- / Ox 0 6 David Marketing Manager /1/ 0x58 1 46 Sariya Marketing Specialist
So erstellen Sie ein Verfahren dafür, neue Knoten einzufügen
Um die Eingabe der Daten zu vereinfachen, erstellen Sie die folgende gespeicherte Prozedur, um Mitarbeiterdaten in die Tabelle EmployeeOrg einzufügen. Die Prozedur akzeptiert Eingabewerte über den Mitarbeiter, der hinzugefügt wird. Diese Daten bestehen aus der EmployeeID des Vorgesetzten des neuen Mitarbeiters, der EmployeeID des neuen Mitarbeiters, seinem Vornamen und seinem Titel. Die Prozedur verwendet GetDescendant() und auch die GetAncestor ()-Methode. Führen Sie den folgenden Code aus, um die Prozedur zu erstellen:
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
Im folgenden Beispiel werden die verbliebenen 4 Mitarbeiter, die David direkt oder indirekt berichten, hinzugefügt.
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' ;
Führen Sie auch hier wieder die folgende Abfrage aus, um die Zeilen in der Tabelle EmployeeOrg zu untersuchen:
SELECT OrgNode.ToString() AS Text_OrgNode, OrgNode, OrgLevel, EmployeeID, EmpName, Title FROM HumanResources.EmployeeOrg ; GO
Dies ist das Resultset.
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
Die Tabelle ist jetzt vollständig mit den Daten der Marketingabteilung aufgefüllt.