Les 2: Gegevens maken en beheren in een hiërarchische tabel

Artikel
02/04/2025

van toepassing op:SQL Server Azure SQL Database Azure SQL Managed Instance SQL-database in Microsoft Fabric

In les 1 hebt u een bestaande tabel gewijzigd om de hiërarchie-id gegevenstype te gebruiken en de hiërarchie-id kolom gevuld met de weergave van de bestaande gegevens. In deze les begint u met een nieuwe tabel en voegt u gegevens in met behulp van de hiërarchische methoden. Vervolgens voert u query's uit en bewerkt u de gegevens met behulp van de hiërarchische methoden.

Voorwaarden

Voor het voltooien van deze zelfstudie hebt u SQL Server Management Studio nodig, toegang tot een server waarop SQL Server wordt uitgevoerd en een AdventureWorks2022-database.

Installeer SQL Server Management Studio (SSMS).
Installeer SQL Server 2022 Developer Edition.
Download AdventureWorks-voorbeelddatabase.

Instructies voor het herstellen van databases in SSMS zijn hier: Een databaseback-up herstellen met behulp van SSMS.

Een tabel maken met behulp van het gegevenstype Hierarchyid

In het volgende voorbeeld wordt een tabel met de naam EmployeeOrggemaakt, die werknemersgegevens samen met hun rapportagehiërarchie bevat. In het voorbeeld wordt de tabel gemaakt in de AdventureWorks2022-database, maar dat is optioneel. Als u het voorbeeld eenvoudig wilt houden, bevat deze tabel slechts vijf kolommen:

OrgNode is een hiërarchie-id kolom waarin de hiërarchische relatie wordt opgeslagen.
OrgLevel is een berekende kolom op basis van de OrgNode kolom waarin elk knooppuntniveau in de hiërarchie wordt opgeslagen. Het wordt gebruikt voor een breedte-eerste index.
EmployeeID bevat het typische identificatienummer van werknemers dat wordt gebruikt voor toepassingen zoals salarisadministratie. Bij het ontwikkelen van nieuwe toepassingen kunnen toepassingen de kolom OrgNode gebruiken en deze afzonderlijke EmployeeID kolom is niet nodig.
EmpName bevat de naam van de werknemer.
Title bevat de titel van de werknemer.

De tabel EmployeeOrg maken

Voer in een queryeditorvenster de volgende code uit om de EmployeeOrg tabel te maken. Als u de kolom OrgNode opgeeft als de primaire sleutel met een geclusterde index, wordt een diepte-eerste index gemaakt:

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

Voer de volgende code uit om een samengestelde index te maken voor de kolommen OrgLevel en OrgNode ter ondersteuning van efficiënte zoekopdrachten in de breedte:
```
CREATE UNIQUE INDEX EmployeeOrgNc1
ON HumanResources.EmployeeOrg(OrgLevel, OrgNode);
GO
```

De tabel is nu gereed voor gegevens. Met de volgende taak wordt de tabel gevuld met behulp van hiërarchische methoden.

Een hiërarchische tabel vullen met behulp van hiërarchische methoden

AdventureWorks2022 heeft acht werknemers die werkzaam zijn op de afdeling Marketing. De werknemershiërarchie ziet er als volgt uit:

David, EmployeeID 6, is de marketingmanager. Drie marketingspecialisten rapporteren aan David:

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

Marketingassistent Wanida (EmployeeID 269), rapporteert aan Sariyaen marketingassistent Mary (EmployeeID 272), rapporten aan John.

De wortel van de hiërarchie invoegen

In het volgende voorbeeld wordt David de Marketing Manager ingevoegd in de tabel aan de wortel van de hiërarchie. De OrdLevel kolom is een berekende kolom. Daarom maakt het geen deel uit van de INSERT-verklaring. Deze eerste gegevensrecord maakt gebruik van de GetRoot -methode (Database Engine) om deze eerste gegevensrecord te vullen als de wortel van de hiërarchie.
```
INSERT HumanResources.EmployeeOrg (OrgNode, EmployeeID, EmpName, Title)
VALUES (hierarchyid::GetRoot(), 6, 'David', 'Marketing Manager');
GO
```

Voer de volgende code uit om de eerste rij in de tabel te onderzoeken:

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

Hier is het resultatenoverzicht.

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

Net als in de vorige les gebruiken we de methode ToString() om de hiërarchie-id gegevenstype te converteren naar een indeling die gemakkelijker te begrijpen is.

Een onderliggende werknemer invoegen

Sariya rapporteert aan David. Als u Sariya's knooppunt wilt invoegen, moet u een geschikte OrgNode waarde van het gegevenstype hiërarchie-idmaken. Met de volgende code wordt een variabele van het gegevenstype hierarchyid gemaakt en wordt deze gevuld met de hoofdwaarde OrgNode van de tabel. Vervolgens wordt die variabele gebruikt met de GetDescendant (Database Engine) methode om rij in te voegen die een ondergeschikt knooppunt is. GetDescendant heeft twee argumenten. Bekijk de volgende opties voor de argumentwaarden:
- Als ouder NULLis, retourneert GetDescendantNULL.
- Als ouder niet NULLis en zowel child1 als child2NULLzijn, retourneert GetDescendant een kind van ouder.
- Als ouder en child1 niet NULLzijn en child2NULLis, retourneert GetDescendant een kindelement van ouder dat groter is dan child1.
- Als de ouder en child2 niet NULL zijn en child1 is NULL, retourneert GetDescendant een kind van de ouder dat kleiner is dan child2.
- Als ouder, child1en child2 allemaal niet NULLzijn, retourneert GetDescendant een kindelement van ouder dat groter is dan child1 en kleiner is dan child2.
In de volgende code worden de (NULL, NULL) argumenten van de wortelouder gebruikt, omdat er nog geen rijen in de tabel staan, behalve de root. Voer de volgende code uit om Sariyain te voegen:
```
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');
```

Herhaal de query uit de eerste procedure om een query uit te voeren op de tabel en kijk hoe de vermeldingen worden weergegeven:

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

Hier is het resultaat.

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

Een procedure maken voor het invoeren van nieuwe knooppunten

Als u het invoeren van gegevens wilt vereenvoudigen, maakt u de volgende opgeslagen procedure om werknemers toe te voegen aan de EmployeeOrg tabel. De procedure accepteert invoerwaarden over de werknemer die wordt toegevoegd. Dit omvat de EmployeeID van de manager van de nieuwe werknemer, het EmployeeID nummer van de nieuwe werknemer en hun voornaam en titel. De procedure maakt gebruik van GetDescendant() en ook de methode GetAncestor (Database Engine). Voer de volgende code uit om de procedure te maken:

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

In het volgende voorbeeld worden de overige vier werknemers toegevoegd die direct of indirect aan Davidrapporteren.

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

Voer opnieuw de volgende query uit om de rijen in de EmployeeOrg tabel te onderzoeken:

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

Hier is het resultaat.

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

De tabel is nu volledig gevuld met de marketingorganisatie.

Query's uitvoeren op een hiërarchische tabel met behulp van hiërarchiemethoden

Nu de tabel HumanResources.EmployeeOrg volledig is ingevuld, ziet u met deze taak hoe u query's kunt uitvoeren op de hiërarchie met behulp van een aantal hiërarchische methoden.

Onderliggende knooppunten zoeken

Sariya heeft één ondergeschikte werknemer. Als u een query wilt uitvoeren op de ondergeschikten van Sariya, voert u de volgende query uit die gebruikmaakt van de IsDescendantOf (Database Engine) methode:
```
DECLARE @CurrentEmployee HIERARCHYID

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

SELECT *
FROM HumanResources.EmployeeOrg
WHERE OrgNode.IsDescendantOf(@CurrentEmployee ) = 1;
```
Het resultaat bevat zowel Sariya als Wanida. Sariya wordt vermeld omdat deze waarde de afstammeling is op 0 niveau. Wanida is de afstammeling op het 1-niveau.
U kunt deze informatie ook opvragen met behulp van de GetAncestor -methode (Database Engine). GetAncestor gebruikt een argument voor het niveau dat u wilt retourneren. Aangezien Wanida één niveau onder Sariya is, gebruikt u GetAncestor(1) zoals wordt gedemonstreerd in de volgende code:
```
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
```
Deze keer geeft het resultaat alleen Wanida weer.

Wijzig nu de @CurrentEmployee in David (EmployeeID 6) en het niveau in 2. Voer het volgende uit om ook Wanida te retourneren:

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

Deze keer ontvangt u ook Mary die ook rapporteert aan David, twee niveaus omlaag.

GetRoot en GetLevel gebruiken

Naarmate de hiërarchie groter wordt, is het moeilijker om te bepalen waar de leden zich in de hiërarchie bevinden. Gebruik de GetLevel (Database Engine) methode om te bepalen hoeveel niveaus omlaag elke rij zich in de hiërarchie bevindt. Voer de volgende code uit om de niveaus van alle rijen weer te geven:
```
SELECT OrgNode.ToString() AS Text_OrgNode,
OrgNode.GetLevel() AS EmpLevel, *
FROM HumanResources.EmployeeOrg;
GO
```
Gebruik de GetRoot (Database Engine) methode om het hoofdknooppunt in de hiërarchie te vinden. De volgende code retourneert de enkele rij, die de wortel is:
```
SELECT OrgNode.ToString() AS Text_OrgNode, *
FROM HumanResources.EmployeeOrg
WHERE OrgNode = HIERARCHYID::GetRoot();
GO
```

Gegevens in een hiërarchische tabel opnieuw ordenen met behulp van hiërarchische methoden

van toepassing op: SQL Server

Het herorganiseren van een hiërarchie is een algemene onderhoudstaak. In deze taak gebruiken we een UPDATE instructie met de methode GetReparentedValue (Database Engine) om eerst één rij naar een nieuwe locatie in de hiërarchie te verplaatsen. Vervolgens verplaatsen we een hele substructuur naar een nieuwe locatie.

De methode GetReparentedValue heeft twee argumenten. Het eerste argument beschrijft het deel van de hiërarchie dat moet worden gewijzigd. Als een hiërarchie bijvoorbeeld is /1/4/2/3/ en u de sectie /1/4/ wilt wijzigen, wordt de hiërarchie /2/1/2/3/, waardoor de laatste twee knooppunten (2/3/) ongewijzigd blijven, moet u de veranderende knooppunten (/1/4/) opgeven als het eerste argument. Het tweede argument biedt het nieuwe hiërarchieniveau, in ons voorbeeld /2/1/. De twee argumenten hoeven niet hetzelfde aantal niveaus te bevatten.

Eén rij verplaatsen naar een nieuwe locatie in de hiërarchie

Momenteel rapporteert Wanida aan Sariya. In deze procedure verplaatst u Wanida van het huidige knooppunt /1/1/, zodat deze persoon rapporteert aan Jill. Het nieuwe knooppunt wordt /3/1/ zodat /1/ het eerste argument is en /3/ het tweede is. Deze komen overeen met de OrgNode waarden van Sariya en Jill. Voer de volgende code uit om Wanida te verplaatsen van de organisatie van Sariya naar 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

Voer de volgende code uit om het resultaat te zien:

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

Wanida bevindt zich nu op knooppunt /3/1/.

Een sectie van een hiërarchie opnieuw organiseren

Als u wilt laten zien hoe u een groter aantal personen tegelijk verplaatst, voert u eerst de volgende code uit om een intern rapportage toe te voegen aan Wanida:
```
EXEC AddEmp 269, 291, 'Kevin', 'Marketing Intern';
GO
```

Kevin rapporteert nu aan Wanida, die rapporteert aan Jill, die rapporteert aan David. Dat betekent dat Kevin op niveau /3/1/1/is. Om alle ondergeschikten van Jill naar een nieuwe manager te verplaatsen, werken we alle knooppunten bij die /3/ als hun OrgNode hebben, naar een nieuwe waarde. Voer de volgende code uit om Wanida aan te passen zodat zij aan Sariya rapporteert, maar zorg ervoor dat Kevin aan Wanida blijft rapporteren.

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;