Leçon 2 : Créer et gérer des données dans une table hiérarchique

Article
08/01/2024

S’applique à : SQL Server Azure SQL Database Azure SQL Managed Instance

Dans la leçon 1, vous avez modifié une table existante pour utiliser le type de données hierarchyid et vous avez rempli la colonne hierarchyid avec la représentation des données existantes. Dans cette leçon, vous allez utiliser les méthodes hiérarchiques pour créer une nouvelle table et y insérer des données. Puis, toujours à l'aide de méthodes hiérarchiques, vous interrogerez et manipulerez ces données.

Prérequis

Pour suivre ce tutoriel, vous avez besoin de SQL Server Management Studio, de l'accès à un serveur qui exécute SQL Server et d'une base de données AdventureWorks2022.

Installez SQL Server Management Studio (SSMS).
Installez SQL Server 2022 Developer Edition.
Téléchargez l'exemple de bases de données AdventureWorks.

Les instructions de restauration de bases de données dans SSMS se trouvent ici : Restaurer une sauvegarde de base de données à l’aide de SSMS.

Créer une table à l’aide du type de données hierarchyid

L'exemple suivant crée une table nommée EmployeeOrg qui inclut des données sur les employés ainsi que leur hiérarchie de création de rapports. Cet exemple crée la table dans la base de données AdventureWorks2022 , mais cela est facultatif. Pour que l'exemple reste simple, cette table ne comporte que cinq colonnes :

OrgNode est une colonne hierarchyid qui stocke la relation hiérarchique.
OrgLevel est une colonne calculée en fonction de la colonne OrgNode qui stocke chaque niveau de nœuds dans la hiérarchie. Elle est utilisée pour un index à largeur prioritaire.
EmployeeID contient le numéro d'identification d'employé qui est habituellement utilisé pour les applications telles que les salaires. Dans le nouveau développement d'applications, les applications peuvent utiliser la colonne OrgNode ; cette colonne EmployeeID séparée n'est donc pas nécessaire.
EmpName contient le nom de l'employé.
Title contient le titre de l'employé.

Créer la table EmployeeOrg

Dans une fenêtre de l'Éditeur de requête, exécutez le code suivant pour créer la table EmployeeOrg . Lorsque la colonne OrgNode est définie comme clé primaire avec un index cluster, un index à profondeur prioritaire est créé :

USE AdventureWorks2022;
GO

IF OBJECT_ID('HumanResources.EmployeeOrg') IS NOT NULL
    DROP TABLE HumanResources.EmployeeOrg

CREATE TABLE HumanResources.EmployeeOrg (
    OrgNode HIERARCHYID PRIMARY KEY CLUSTERED,
    OrgLevel AS OrgNode.GetLevel(),
    EmployeeID INT UNIQUE NOT NULL,
    EmpName VARCHAR(20) NOT NULL,
    Title VARCHAR(20) NULL
);
GO

Exécutez le code suivant pour créer un index composite sur les colonnes OrgLevel et OrgNode afin de prendre en charge des recherches à largeur prioritaire efficaces :
```
CREATE UNIQUE INDEX EmployeeOrgNc1
ON HumanResources.EmployeeOrg(OrgLevel, OrgNode);
GO
```

La table est maintenant prête à recevoir les données. La tâche suivante remplira la table à l'aide de méthodes hiérarchiques.

Remplir une table hiérarchique en utilisant des méthodes hiérarchiques

AdventureWorks2022 a 8 employés travaillant au service Marketing. La hiérarchie des employés se présente comme suit :

David, EmployeeID 6, est le directeur du marketing. David est le supérieur de trois spécialistes en marketing :

Sariya, EmployeeID 46
John, EmployeeID 271
Jill, EmployeeID 119

Sariya est la supérieure de l’assistante marketing Wanida (EmployeeID 269) et John est le supérieur de l’assistante marketing Mary (EmployeeID 272).

Insérer la racine de la structure hiérarchique

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 (moteur de base de données) 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
```

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 le jeu de résultats obtenu.

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.

Insérer un employé subordonné

Sariya est la subordonnée de David. Pour insérer le nœud de Sariya's, 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. Il utilise ensuite cette variable avec la méthode GetDescendant (moteur de base de données) pour insérer une ligne qui est un nœud subordonné. GetDescendant nécessite deux arguments. Vérifiez les valeurs d'argument des options suivantes :
- Si le parent est NULL, GetDescendant retourne NULL.
- Si le parent n’est pas NULL, et que child1 et child2 sont NULL, GetDescendant retourne un enfant de parent.
- Si le parent et child1 ne sont pas NULL, et child2 est NULL, GetDescendant retourne un enfant de parent supérieur à child1.
- Si le parent et child2 ne sont pas NULL et child1 est NULL, GetDescendant retourne un enfant de parent inférieur à child2.
- Si le parent, child1 et child2 ne sont pas NULL, GetDescendant retourne un enfant de parent supérieur à child1 et inférieur à child2.
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');
```

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 le jeu de résultats obtenu.

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

Créer une procédure pour entrer de nouveaux nœuds

Pour simplifier la saisie 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 (moteur de base de données). Exécutez le code suivant pour créer la procédure :

CREATE PROCEDURE 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

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

Réexécutez 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 le jeu de résultats obtenu.

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

La table est maintenant complètement remplie avec l'organisation du service Marketing.

Interroger une table hiérarchique à l’aide de méthodes hiérarchiques

Maintenant que la table HumanResources.EmployeeOrg est entièrement remplie, cette tâche vous indique comment interroger la hiérarchie à l'aide de certaines des méthodes hiérarchiques.

Rechercher des nœuds subordonnés

Sariya a un employé subordonné. Pour rechercher les subordonnés de Sariya, exécutez la requête suivante qui utilise la méthode IsDescendantOf (moteur de base de données) :
```
DECLARE @CurrentEmployee HIERARCHYID

SELECT @CurrentEmployee = OrgNode
FROM HumanResources.EmployeeOrg
WHERE EmployeeID = 46;

SELECT *
FROM HumanResources.EmployeeOrg
WHERE OrgNode.IsDescendantOf(@CurrentEmployee ) = 1;
```
Le résultat répertorie à la fois Sariya et Wanida. Sariya est répertoriée car cette valeur est la descendante au niveau 0. Wanida est la descendante au niveau 1.
Vous pouvez également créer des requêtes sur ces informations à l’aide de la méthode GetAncestor (moteur de base de données). GetAncestor prend un argument pour le niveau que vous tentez de retourner. Étant donné que Wanida est un niveau plus bas que Sariya, utilisez GetAncestor(1) comme indiqué dans le code suivant :
```
DECLARE @CurrentEmployee HIERARCHYID

SELECT @CurrentEmployee = OrgNode
FROM HumanResources.EmployeeOrg
WHERE EmployeeID = 46;

SELECT OrgNode.ToString() AS Text_OrgNode, *
FROM HumanResources.EmployeeOrg
WHERE OrgNode.GetAncestor(1) = @CurrentEmployee
```
Cette fois-ci, le résultat répertorie uniquement Wanida.
Modifiez maintenant @CurrentEmployee sur David (EmployeeID 6) et le niveau sur 2. Exécutez le code suivant pour retourner également Wanida :
```
DECLARE @CurrentEmployee HIERARCHYID

SELECT @CurrentEmployee = OrgNode
FROM HumanResources.EmployeeOrg
WHERE EmployeeID = 6;

SELECT OrgNode.ToString() AS Text_OrgNode, *
FROM HumanResources.EmployeeOrg
WHERE OrgNode.GetAncestor(2) = @CurrentEmployee
```
Cette fois-ci, le résultat répertorie également Mary, deux niveaux plus bas, dont David est également le supérieur.

Utiliser GetRoot et GetLevel

À mesure que la hiérarchie s'agrandit, il devient plus difficile de déterminer l'emplacement des membres dans la hiérarchie. La méthode GetLevel (moteur de base de données) permet de déterminer le nombre de niveaux sous chaque ligne dans la hiérarchie. Exécutez le code suivant pour consulter les niveaux de toutes les lignes :
```
SELECT OrgNode.ToString() AS Text_OrgNode,
OrgNode.GetLevel() AS EmpLevel, *
FROM HumanResources.EmployeeOrg;
GO
```
La méthode GetRoot (moteur de base de données) permet de rechercher le nœud racine dans la hiérarchie. Le code suivant retourne la ligne unique qui est la racine :
```
SELECT OrgNode.ToString() AS Text_OrgNode, *
FROM HumanResources.EmployeeOrg
WHERE OrgNode = HIERARCHYID::GetRoot();
GO
```

Réorganiser des données dans une table hiérarchique à l'aide de méthodes hiérarchiques

S'applique à : SQL Server

La réorganisation d'une hiérarchie est une tâche de maintenance courante. Dans cette tâche, nous utilisons une instruction UPDATE avec la méthode GetReparentedValue (moteur de base de données) pour déplacer en premier lieu une seule ligne vers un nouvel emplacement dans la hiérarchie. Nous déplaçons ensuite la totalité d'une sous-arborescence vers un nouvel emplacement.

La méthode GetReparentedValue accepte deux arguments. Le premier argument décrit la partie de la hiérarchie à modifier. Par exemple, si une hiérarchie est /1/4/2/3/ et que vous souhaitez modifier la section /1/4/, la hiérarchie devient /2/1/2/3/, laissant les deux derniers nœuds (2/3/) inchangés. Les nœuds à modifier (/1/4/) doivent être spécifiés comme premier argument. Le deuxième argument fournit le nouveau niveau de hiérarchie, dans notre exemple /2/1/. Les deux arguments ne doivent pas nécessairement contenir le même nombre de niveaux.

Déplacer une ligne unique vers un nouvel emplacement dans la hiérarchie

Sariya est actuellement la supérieure de Wanida. Dans cette procédure, vous déplacez Wanida du nœud /1/1/, où elle se trouve actuellement, pour que Jill soit sa supérieure. Le nouveau nœud devient ainsi /3/1/. /1/ est donc le premier argument et /3/ le second. Ceux-ci correspondent aux valeurs OrgNode de Sariya et de Jill. Exécutez le code suivant pour déplacer Wanida de l'organisation de Sariya vers celle de Jill :

DECLARE @CurrentEmployee HIERARCHYID,
    @OldParent HIERARCHYID,
    @NewParent HIERARCHYID;

SELECT @CurrentEmployee = OrgNode
FROM HumanResources.EmployeeOrg
WHERE EmployeeID = 269;

SELECT @OldParent = OrgNode
FROM HumanResources.EmployeeOrg
WHERE EmployeeID = 46;

SELECT @NewParent = OrgNode
FROM HumanResources.EmployeeOrg
WHERE EmployeeID = 119;

UPDATE HumanResources.EmployeeOrg
SET OrgNode = @CurrentEmployee.GetReparentedValue(@OldParent, @NewParent)
WHERE OrgNode = @CurrentEmployee;
GO

Exécutez le code suivant pour afficher le résultat :

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

Wanida se trouve maintenant au nœud /3/1/.

Réorganiser une section d’une hiérarchie

Pour montrer comment déplacer simultanément un plus grand nombre de personnes, exécutez d'abord le code suivant pour ajouter un subalterne interne à Wanida :
```
EXEC AddEmp 269, 291, 'Kevin', 'Marketing Intern';
GO
```

Kevin est désormais le subalterne de Wanida, elle-même subalterne de Jill, elle-même subalterne de David. Cela signifie que Kevin est au niveau /3/1/1/. Pour déplacer tous les subalternes de Jill vers un nouveau responsable, nous mettons à jour tous les nœuds qui ont /3/ comme valeur OrgNode vers une nouvelle valeur. Exécutez le code suivant pour mettre à jour Wanida de sorte que Sariya soit sa supérieure, en conservant Kevin comme subalterne de Wanida :

DECLARE @OldParent HIERARCHYID,
    @NewParent HIERARCHYID

SELECT @OldParent = OrgNode
FROM HumanResources.EmployeeOrg
WHERE EmployeeID = 119;-- Jill

SELECT @NewParent = OrgNode
FROM HumanResources.EmployeeOrg
WHERE EmployeeID = 46;-- Sariya

DECLARE children_cursor CURSOR
FOR
SELECT OrgNode
FROM HumanResources.EmployeeOrg
WHERE OrgNode.GetAncestor(1) = @OldParent;

DECLARE @ChildId HIERARCHYID;

OPEN children_cursor

FETCH NEXT
FROM children_cursor
INTO @ChildId;

WHILE @@FETCH_STATUS = 0
BEGIN
    START:

    DECLARE @NewId HIERARCHYID;

    SELECT @NewId = @NewParent.GetDescendant(MAX(OrgNode), NULL)
    FROM HumanResources.EmployeeOrg
    WHERE OrgNode.GetAncestor(1) = @NewParent;

    UPDATE HumanResources.EmployeeOrg
    SET OrgNode = OrgNode.GetReparentedValue(@ChildId, @NewId)
    WHERE OrgNode.IsDescendantOf(@ChildId) = 1;

    IF @@error <> 0
        GOTO START -- On error, retry

    FETCH NEXT
    FROM children_cursor
    INTO @ChildId;
END

CLOSE children_cursor;

DEALLOCATE children_cursor;

Exécutez le code suivant pour afficher le résultat :

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

Voici le jeu de résultats obtenu.

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
/1/1/1/      0x5AD0  3        291        Kevin   Marketing Intern
/2/          0x68    1        271        John    Marketing Specialist
/2/1/        0x6AC0  2        272        Mary    Marketing Assistant
/3/          0x78    1        119        Jill    Marketing Specialist

La totalité de l'arborescence de l'organisation dont Jill était la supérieure (Wanida et Kevin) a maintenant Sariya comme supérieure.

Pour obtenir une procédure stockée pour réorganiser une section d’une hiérarchie, consultez la section Déplacer des sous-arborescences de Données hiérarchiques (SQL Server).

Partager via

Leçon 2 : Créer et gérer des données dans une table hiérarchique

Prérequis

Créer une table à l’aide du type de données hierarchyid

Créer la table EmployeeOrg

Remplir une table hiérarchique en utilisant des méthodes hiérarchiques

Insérer la racine de la structure hiérarchique

Insérer un employé subordonné

Créer une procédure pour entrer de nouveaux nœuds

Interroger une table hiérarchique à l’aide de méthodes hiérarchiques

Rechercher des nœuds subordonnés

Utiliser GetRoot et GetLevel

Réorganiser des données dans une table hiérarchique à l'aide de méthodes hiérarchiques

Déplacer une ligne unique vers un nouvel emplacement dans la hiérarchie

Réorganiser une section d’une hiérarchie

Commentaires

Ressources supplémentaires