Lektion 2: Erstellen und Verwalten von Daten in einer hierarchischen Tabelle

Artikel
07/31/2024

Gilt für: SQL Server Azure SQL-Datenbank Azure SQL Managed Instance

In Lektion 1 haben Sie eine vorhandene Tabelle so geändert, dass sie den hierarchyid -Datentyp verwendet. Dann haben Sie die hierarchyid -Spalte entsprechend der in den vorhandenen Daten gegebenen hierarchischen Darstellung gefüllt. In dieser Lektion erstellen Sie eine neue Tabelle und verwenden hierarchische Methoden, um Daten in sie einzufügen. Dann fragen Sie Daten ab und bearbeiten sie, indem Sie hierarchische Methoden verwenden.

Voraussetzungen

Zur Durchführung dieses Tutorials benötigen Sie SQL Server Management Studio, Zugriff auf einen Server, auf dem SQL-Server ausgeführt wird, und eine AdventureWorks2022-Datenbank.

Installieren Sie SQL Server Management Studio (SSMS).
Installieren Sie die SQL Server 2022 Developer Edition.
Laden Sie eine AdventureWorks-Beispieldatenbank herunter.

Anweisungen zum Wiederherstellen von Datenbanken in SSMS finden Sie hier: Wiederherstellen eines Datenbank-Backups mit SSMS.

Erstellen einer Tabelle mit dem Datentyp „hierarchyid“

Im folgenden Beispiel wird eine Tabelle namens EmployeeOrg erstellt, die Mitarbeiterdaten zusammen mit ihrer Berichtshierarchie aufnimmt. Das Beispiel erstellt die neue Tabelle in der Datenbank AdventureWorks2022 ; dies ist jedoch optional. Um das Beispiel einfach zu halten, enthält die Tabelle nur fünf Spalten:

OrgNode ist eine hierarchyid-Spalte, die die hierarchische Beziehung speichert.
OrgLevel ist eine auf der Spalte OrgNode basierende berechnete Spalte, die die Ebene in der Hierarchie speichert. Sie wird für einen Breitensuchindex verwendet.
EmployeeID enthält die typische Mitarbeiter-ID, die für Anwendungen wie beispielsweise die Gehaltsdaten verwendet wird. Bei der Entwicklung neuer Anwendungen können diese die Spalte OrgNode verwenden, und die eigene Spalte EmployeeID wird nicht benötigt.
EmpName enthält den Namen des Angestellten.
Title enthält den Titel des Angestellten.

Erstellen der Tabelle „EmployeeOrg“

Führen Sie in einem Abfrage-Editorfenster den folgenden Code aus, um die Tabelle EmployeeOrg zu erstellen. Wenn Sie die Spalte OrgNode als Primärschlüssel mit einem gruppierten Index angeben, wird ein Tiefensuchindex erstellt:

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

Führen Sie den folgenden Code aus, um einen zusammengesetzten Index für die Spalten OrgLevel und OrgNode zu erstellen, der effiziente Breitensuchoperationen unterstützt:
```
CREATE UNIQUE INDEX EmployeeOrgNc1
ON HumanResources.EmployeeOrg(OrgLevel, OrgNode);
GO
```

Die Tabelle ist jetzt bereit, Daten zu speichern. Die nächste Aufgabe besteht darin, die Tabelle mithilfe hierarchischer Methoden aufzufüllen.

Auffüllen einer hierarchischen Tabelle mit hierarchischen Methoden

AdventureWorks2022 hat acht Mitarbeiter, die in der Marketingabteilung arbeiten. Die Angestelltenhierarchie sieht ungefähr wie folgt aus:

David, EmployeeID 6, ist der Marketing Manager. Drei Marketingspezialisten berichten an David:

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

Marketing-Assistent Wanida (EmployeeID 269) berichtet an Sariya und Marketing-Assistent Mary (EmployeeID 272) berichtet an John.

Einfügen des Stamms in die Hierarchiestruktur

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 (Datenbank-Engine), 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;

Hier sehen Sie das Ergebnis.

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.

Einfügen eines unterstellten Mitarbeiters

Sariya berichtet an David. Um Sariya's-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 (Datenbank-Engine) verwendet, um eine Zeile einzufügen, die den untergeordneten Knoten darstellt. GetDescendant übernimmt zwei Argumente. Für die Argumentwerte gelten die folgenden Optionen:
- Wenn das übergeordnete Element NULL lautet, gibt GetDescendant NULL zurück.
- Wenn das übergeordnete Element nicht NULL ist und sowohl child1 als auch child2 NULL sind, gibt GetDescendant ein untergeordnetes Element des übergeordneten Elements zurück.
- Wenn das übergeordnete Element und child1 nicht NULL sind, und child2 ist NULL, gibt GetDescendant ein untergeordnetes Element des übergeordneten Elements zurück, das größer als child1 ist.
- Wenn das übergeordnete Element und child2 nicht NULL sind und child1 ist NULL, gibt GetDescendant ein untergeordnetes Element des übergeordneten Elements zurück, das kleiner als child2 ist.
- Sind übergeordnetes Element, child1 und child2 alle nicht NULL, dann gibt GetDescendant ein untergeordnetes Element des übergeordneten Elements zurück, das 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;

Hier sehen Sie das Ergebnis.

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

Erstellen eines Verfahrens zum Einfügen neuer Knoten

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 (Datenbank-Engine)-Methode. Führen Sie den folgenden Code aus, um die Prozedur zu erstellen:

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

Im folgenden Beispiel werden die verbliebenen vier Mitarbeiter, die direkt oder indirekt an David 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

Hier sehen Sie das Ergebnis.

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.

Abfragen einer hierarchischen Tabelle mit hierarchischen Methoden

Nachdem die Tabelle HumanResources.EmployeeOrg nun vollständig gefüllt ist, zeigt Ihnen diese Aufgabe, wie Sie die Hierarchie mithilfe einiger der hierarchischen Methoden abfragen können.

Suchen untergeordneter Knoten

Sariya ist ein Mitarbeiter unterstellt. Um die Sariya unterstellten Mitarbeiter abzufragen, führen Sie die folgende Abfrage aus, die die IsDescendantOf (Datenbank-Engine)-Methode verwendet:
```
DECLARE @CurrentEmployee HIERARCHYID

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

SELECT *
FROM HumanResources.EmployeeOrg
WHERE OrgNode.IsDescendantOf(@CurrentEmployee ) = 1;
```
Das Ergebnis listet sowohl Sariya als auch Wanida auf. Sariya wird aufgelistet, weil der Wert Nachfolger auf Ebene 0 ist. Wanida ist Nachfolger auf Ebene 1.
Sie können diese Informationen auch mit der GetAncestor (Datenbank-Engine)-Methode abfragen. GetAncestor übernimmt ein Argument für die Ebene, die zurückgegeben werden soll. Da Wanida eine Ebene unter Sariya angesiedelt ist, können Sie GetAncestor(1) verwenden, wie der folgende Code veranschaulicht:
```
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
```
Dieses Mal listet das Ergebnis nur Wanida auf.

Ändern Sie jetzt @CurrentEmployee in David (EmployeeID 6) und die Ebene in 2. Führen Sie folgenden Code aus, um auch Wanida zurückzugeben:

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

Dieses Mal erhalten Sie auch Mary, die, zwei Ebenen darunter, ebenfalls David unterstellt ist.

Verwenden von „GetRoot“ und „GetLevel“

Mit dem Anwachsen der Hierarchie wird es schwieriger zu ermitteln, wo innerhalb der Hierarchie sich die Elemente befinden. Verwenden Sie die GetLevel (Datenbank-Engine)-Methode, um zu ermitteln, auf welcher Ebene der Hierarchie sich eine Zeile befindet. Führen Sie den folgenden Code aus, um die Ebenen aller Zeilen anzuzeigen:
```
SELECT OrgNode.ToString() AS Text_OrgNode,
OrgNode.GetLevel() AS EmpLevel, *
FROM HumanResources.EmployeeOrg;
GO
```
Verwenden Sie die GetRoot (Datenbank–Engine)-Methode, um den Stammknoten in der Hierarchie zu ermitteln. Im folgenden Code wird die einzelne Zeile, die der Stamm ist, zurückgegeben:
```
SELECT OrgNode.ToString() AS Text_OrgNode, *
FROM HumanResources.EmployeeOrg
WHERE OrgNode = HIERARCHYID::GetRoot();
GO
```

Neuanordnen von Daten in einer hierarchischen Tabelle mit hierarchischen Methoden

Gilt für: SQL Server

Eine Hierarchie neu zu ordnen, ist eine allgemeine Wartungsaufgabe. In dieser Aufgabe werden wir eine UPDATE-Anweisung mit der GetReparentedValue (Datenbank-Engine)-Methode verwenden, um zunächst eine einzelne Zeile an eine neue Position in der Hierarchie zu verschieben. Dann verschieben wir eine ganze Teilstruktur an eine neue Position.

Die GetReparentedValue -Methode benötigt zwei Argumente. Das erste Argument beschreibt den Teil der Hierarchie, der geändert werden soll. Möchten Sie zum Beispiel in der Hierarchie /1/4/2/3/ den Abschnitt /1/4/ ändern, dann wird die Hierarchie zu /2/1/2/3/, wobei die beiden letzten Knoten (2/3/) unverändert bleiben, und Sie müssen die zu ändernden Knoten (/1/4/) als erstes Argument angeben. Das zweite Argument gibt die neue Hierarchieebene an, in unserem Beispiel /2/1/. Die zwei Argumente dürfen nicht die gleichen Ebenennummern enthalten.

Verschieben einer einzelnen Zeile an eine neue Position in der Hierarchie

Wanida berichtet aktuell Sariya. In dieser Prozedur verschieben Sie Wanida von ihrem aktuellen Knoten /1/1/ so, dass sie an Jill berichtet. Ihr neuer Knoten wird /3/1/, daher ist /1/ das erste Argument und /3/ das zweite. Diese Werte entsprechen den OrgNode-Werten von Sariya und Jill. Führen Sie den folgenden Code aus, um Wanida von Sariyas Organisation in die Jills zu verschieben:

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

Führen Sie den folgenden Code aus, um die Ergebnisse sehen zu können:

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

Wanida ist jetzt dem Knoten /3/1/ zugeordnet.

Reorganisieren eines Abschnitts einer Hierarchie

Um zu veranschaulichen, wie eine größere Anzahl von Leuten gleichzeitig verschoben werden kann, führen Sie zunächst den folgenden Code aus, um einen neuen Mitarbeiter einzufügen, der Wanida berichtet:
```
EXEC AddEmp 269, 291, 'Kevin', 'Marketing Intern';
GO
```

Jetzt berichtet Kevin Wanida, der Jill berichtet, die ihrerseits David berichtet. Das bedeutet, dass sich Kevin auf Ebene /3/1/1/ befindet. Um alle Untergebenen von Jill zu einem neuen Manager zu verschieben, aktualisieren wir alle Knoten mit dem Wert /3/ als ihr OrgNode mit einem neuen Wert. Führen Sie den folgenden Code aus, um Wanida so zu aktualisieren, dass sie Sariya unterstellt ist; Kevin hingegen soll weiterhin Wanida unterstellt sein:

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;

Führen Sie den folgenden Code aus, um die Ergebnisse sehen zu können:

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

Hier sehen Sie das Ergebnis.

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

Die gesamte Organisationsstruktur, die Jill (sowohl Wanida als auch Kevin) berichtet hatte, berichtet jetzt Sariya.

Eine gespeicherte Prozedur zum Neuorganisieren eines Abschnitts einer Hierarchie finden Sie im Abschnitt „Verschieben von Unterstrukturen“ der Hierarchischen Daten (SQL Server).

Freigeben über

Lektion 2: Erstellen und Verwalten von Daten in einer hierarchischen Tabelle

Voraussetzungen

Erstellen einer Tabelle mit dem Datentyp „hierarchyid“

Erstellen der Tabelle „EmployeeOrg“

Auffüllen einer hierarchischen Tabelle mit hierarchischen Methoden

Einfügen des Stamms in die Hierarchiestruktur

Einfügen eines unterstellten Mitarbeiters

Erstellen eines Verfahrens zum Einfügen neuer Knoten

Abfragen einer hierarchischen Tabelle mit hierarchischen Methoden

Suchen untergeordneter Knoten

Verwenden von „GetRoot“ und „GetLevel“

Neuanordnen von Daten in einer hierarchischen Tabelle mit hierarchischen Methoden

Verschieben einer einzelnen Zeile an eine neue Position in der Hierarchie

Reorganisieren eines Abschnitts einer Hierarchie

Feedback

Zusätzliche Ressourcen