Remplissage d'une table hiérarchique utilisant des méthodes hiérarchiques

1. L'exemple suivant permet d'insérer David (directeur du marketing) à la table, à la racine de la structure hiérarchique. La colonne OrdLevel est une colonne calculée. Par conséquent, elle ne fait pas partie de l'instruction INSERT. La méthode GetRoot() est utilisée pour remplir ce premier enregistrement en tant que racine de la structure hiérarchique.

   INSERT HumanResources.EmployeeOrg (OrgNode, EmployeeID, EmpName, Title)
   VALUES (hierarchyid::GetRoot(), 6, 'David', 'Marketing Manager') ;
   GO
   

2. Exécutez le code suivant pour examiner la ligne initiale de la table :

   SELECT OrgNode.ToString() AS Text_OrgNode, 
   OrgNode, OrgLevel, EmployeeID, EmpName, Title 
   FROM HumanResources.EmployeeOrg ;
   

   Voici l'ensemble des résultats.

   Text_OrgNode OrgNode OrgLevel EmployeeID EmpName Title
   ------------ ------- -------- ---------- ------- -----------------
   /            Ox      0        6          David   Marketing Manager
   

Comme dans la leçon précédente, nous allons utiliser la méthode ToString() pour convertir le type de données hierarchyid dans un format plus compréhensible.

1. David est le supérieur de Sariya. Pour insérer le nœud de Sariya, vous devez créer une valeur OrgNode appropriée de type de données hierarchyid. Le code suivant permet de créer une variable de type de données hierarchyid et de la remplir avec la valeur racine OrgNode de la table. On utilise ensuite cette variable avec la méthode GetDescendant() pour insérer une ligne qui est un nœud subordonné. GetDescendant accepte deux arguments. Vérifiez les valeurs d'argument des options suivantes :

   • Si parent est NULL, GetDescendant retourne NULL.

   • Si parent n'est pas NULL et que enfant1 et enfant2 sont NULL, GetDescendant retourne un enfant de parent.

   • Si parent et enfant1 ne sont pas NULL et que enfant2 est NULL, GetDescendant retourne un enfant de parent supérieur à enfant1.

   • Si parent et enfant2 ne sont pas NULL et que enfant1 est NULL, GetDescendant retourne un enfant de parent inférieur à enfant2.

   • Si parent, enfant1 et enfant 2 ne sont pas NULL, GetDescendant retourne un enfant de parent supérieur à enfant1 et inférieur à enfant2.

   Le code suivant utilise les arguments (NULL, NULL) du parent racine parce qu'il n'y pas encore de ligne dans la table, à l'exception de la racine. Exécutez le code suivant pour insérer 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') ; 
   

2. Répétez la requête à partir de la première procédure pour interroger la table et voir comment les entrées apparaissent :

   SELECT OrgNode.ToString() AS Text_OrgNode, 
   OrgNode, OrgLevel, EmployeeID, EmpName, Title 
   FROM HumanResources.EmployeeOrg ;
   

   Voici l'ensemble des résultats.

   Text_OrgNode OrgNode OrgLevel EmployeeID EmpName Title
   ------------ ------- -------- ---------- ------- -----------------
   /            Ox      0        6          David   Marketing Manager
   /1/          0x58    1        46         Sariya  Marketing Specialist
   

1. Pour simplifier l'entrée des données, créez la procédure stockée suivante pour ajouter des employés à la table EmployeeOrg. La procédure accepte les valeurs d'entrée relatives à l'employé ajouté. Cela inclut l'EmployeeID du directeur du nouvel employé, le numéro d'EmployeeID du nouvel employé ainsi que leurs prénoms et titres respectifs. La procédure utilise GetDescendant() ainsi que la méthode GetAncestor (). Exécutez le code suivant pour créer la procédure :

   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. L'exemple suivant permet d'ajouter les 4 employés restants dont David est le supérieur direct ou indirect.

   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. Exécutez à nouveau la requête suivante pour vérifier les lignes de la table EmployeeOrg :

   SELECT OrgNode.ToString() AS Text_OrgNode, 
   OrgNode, OrgLevel, EmployeeID, EmpName, Title 
   FROM HumanResources.EmployeeOrg ;
   GO
   

   Voici l'ensemble des résultats.

   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