Auffüllen einer hierarchischen Tabelle mit hierarchischen Methoden

1. 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
   

2. 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.

1. 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') ; 
   

2. 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
   

1. 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
   

2. 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' ;
   

3. 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