Lekcja 2. Tworzenie danych i zarządzanie nimi w tabeli hierarchicznej

Dotyczy:SQL Server Azure SQL Database Azure SQL Managed Instance Baza danych SQL w usłudze Microsoft Fabric

W Lekcji 1 zmodyfikowałeś istniejącą tabelę, aby użyć hierarchyid typu danych, i wypełniłeś kolumnę hierarchyid z reprezentacją istniejących danych. W tej lekcji zaczniesz od nowej tabeli i wstawisz dane przy użyciu metod hierarchicznych. Następnie wykonujesz zapytania dotyczące danych i manipulujesz nimi przy użyciu metod hierarchicznych.

Prerequisites

Do ukończenia tego samouczka potrzebny jest program SQL Server Management Studio, dostęp do serwera z uruchomionym programem SQL Server i bazą danych AdventureWorks2025.

Zainstaluj program SQL Server Management Studio (SSMS).
Zainstaluj program SQL Server 2022 Developer Edition.
Pobierz przykładowe bazy danych AdventureWorks.

Aby uzyskać instrukcje dotyczące przywracania baz danych w programie SSMS, zobacz Przywracanie kopii zapasowej bazy danych przy użyciu programu SSMS.

Tworzenie tabeli przy użyciu typu danych hierarchyid

Poniższy przykład tworzy tabelę o nazwie EmployeeOrg, która zawiera dane pracowników wraz z hierarchią raportowania. Przykład tworzy tabelę w bazie danych AdventureWorks2025, ale jest to opcjonalne. Aby zachować prosty przykład, ta tabela zawiera tylko pięć kolumn:

Kolumna OrgNode jest kolumną typu hierarchyid , która przechowuje relację hierarchiczną.
OrgLevel jest kolumną obliczaną na podstawie kolumny OrgNode, która przechowuje każdy poziom węzłów w hierarchii. Jest on używany dla indeksu pierwszego zakresu.
EmployeeID zawiera typowy numer identyfikacyjny pracownika używany w aplikacjach, takich jak lista płac. W przypadku tworzenia nowych aplikacji aplikacje mogą używać kolumny OrgNode, a ta oddzielna kolumna EmployeeID nie jest potrzebna.
EmpName zawiera nazwę pracownika.
Title zawiera tytuł pracownika.

Tworzenie tabeli EmployeeOrg

W oknie Edytor zapytań uruchom następujący kod, aby utworzyć tabelę EmployeeOrg. Określenie kolumny OrgNode jako klucza podstawowego za pomocą indeksu klastrowanego powoduje utworzenie indeksu pierwszego zakresu:

USE AdventureWorks2022;
GO

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

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

Uruchom następujący kod, aby utworzyć indeks złożony w kolumnach OrgLevel i OrgNode w celu obsługi wydajnych wyszukiwań według zakresu:
```
CREATE UNIQUE INDEX EmployeeOrgNc1
    ON HumanResources.EmployeeOrg(OrgLevel, OrgNode);
```

Tabela jest teraz gotowa do przyjęcia danych. Następne zadanie spowoduje wypełnienie tabeli przy użyciu metod hierarchicznych.

Wypełnianie tabeli hierarchicznej przy użyciu metod hierarchicznych

AdventureWorks2025 ma ośmiu pracowników pracujących w dziale marketingu. Hierarchia pracowników wygląda następująco:

David, EmployeeID 6, jest menedżerem ds. marketingu. Trzech specjalistów ds. marketingu zgłasza David:

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

Asystent marketingu Wanida (EmployeeID 269), raportuje do Sariya, a Asystent marketingu Mary (EmployeeID 272), raportuje do John.

Wstaw korzeń drzewa hierarchii

Poniższy przykład wstawia David menedżera marketingu do tabeli u podstawy hierarchii. Kolumna OrdLevel jest obliczoną kolumną. W związku z tym nie jest częścią oświadczenia INSERT. Ten pierwszy rekord używa metody GetRoot (silnik bazy danych), aby ustawić ten pierwszy rekord jako korzeń hierarchii.
```
INSERT INTO HumanResources.EmployeeOrg (OrgNode, EmployeeID, EmpName, Title)
VALUES (hierarchyid::GetRoot(), 6, 'David', 'Marketing Manager');
```

Wykonaj następujący kod, aby sprawdzić początkowy wiersz w tabeli:

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

Oto zestaw wyników.

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

Podobnie jak w poprzedniej lekcji, użyjemy metody ToString(), aby przekonwertować hierarchyid typ danych na zrozumiały format.

Wstawianie pracownika podrzędnego

Sariya raportuje do David. Aby wstawić węzeł Sariya's, należy utworzyć odpowiednią wartość OrgNode typu danych hierarchyid. Poniższy kod tworzy zmienną typu danych hierarchyid i wypełnia ją wartością root OrgNode tabeli. Następnie używa tej zmiennej z metodą GetDescendant (aparat bazy danych), aby wstawić wiersz, który jest węzłem podrzędnym. GetDescendant przyjmuje dwa argumenty. Przejrzyj następujące opcje dla wartości argumentów:
- Jeśli element nadrzędny jest NULL, GetDescendant zwraca wartość NULL.
- Jeśli element nadrzędny nie jest NULL, a zarówno child1, jak i child2 są NULL, to GetDescendant zwraca dziecko elementu nadrzędnego.
- Jeśli rodzic i child1 nie są NULL, a child2 jest NULL, GetDescendant zwraca dziecko rodzica większego niż child1.
- Jeśli element nadrzędny i child2 nie są NULL, a child1 jest NULL, GetDescendant zwraca element podrzędny elementowi nadrzędnemu mniejszy niż child2.
- Jeśli element nadrzędny, child1i child2 nie są NULL, GetDescendant zwraca dziecko elementu nadrzędnego większe niż child1 i mniejsze niż child2.
Następujący kod używa argumentów (NULL, NULL) korzenia nadrzędnego, ponieważ oprócz korzenia nie ma jeszcze żadnych wierszy w tabeli. Wykonaj następujący kod, aby wstawić Sariya:
```
DECLARE @Manager AS hierarchyid;

SELECT @Manager = hierarchyid::GetRoot()
FROM HumanResources.EmployeeOrg;

INSERT HumanResources.EmployeeOrg (OrgNode, EmployeeID, EmpName, Title)
VALUES (@Manager.GetDescendant(NULL, NULL), 46, 'Sariya', 'Marketing Specialist');
```

Powtórz zapytanie z pierwszej procedury, aby wykonać zapytanie względem tabeli i zobaczyć, jak są wyświetlane wpisy:

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

Oto zestaw wyników.

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

Tworzenie procedury wprowadzania nowych węzłów

Aby uprościć wprowadzanie danych, utwórz następującą procedurę składowaną, aby dodać pracowników do tabeli EmployeeOrg. Procedura akceptuje wartości wejściowe dotyczące dodawanego pracownika. Obejmuje to EmployeeID kierownika nowego pracownika, numer EmployeeID nowego pracownika oraz jego imię i tytuł. Procedura używa GetDescendant(), a także metody GetAncestor (Silnik bazy danych). Wykonaj następujący kod, aby utworzyć procedurę:

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;

W poniższym przykładzie dodano pozostałych czterech pracowników, którzy bezpośrednio lub pośrednio zgłaszają się do David.

EXECUTE AddEmp 6, 271, 'John', 'Marketing Specialist';
EXECUTE AddEmp 6, 119, 'Jill', 'Marketing Specialist';
EXECUTE AddEmp 46, 269, 'Wanida', 'Marketing Assistant';
EXECUTE AddEmp 271, 272, 'Mary', 'Marketing Assistant';

Ponownie wykonaj następujące zapytanie, aby zbadać wiersze w tabeli EmployeeOrg:

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

Oto zestaw wyników.

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

Tabela jest teraz w pełni wypełniona działem marketingu.

Wykonywanie zapytań względem tabeli hierarchicznej przy użyciu metod hierarchii

Teraz, gdy HumanResources.EmployeeOrg tabela jest w pełni wypełniona, to zadanie pokazuje, jak wykonywać zapytania dotyczące hierarchii przy użyciu niektórych metod hierarchicznych.

Znajdowanie węzłów podrzędnych

Sariya ma jednego pracownika podrzędnego. Aby wykonać zapytanie dotyczące podwładnych Sariya, użyj następującego zapytania korzystającego z metody IsDescendantOf (Silnik bazy danych).
```
DECLARE @CurrentEmployee AS hierarchyid;

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

SELECT *
FROM HumanResources.EmployeeOrg
WHERE OrgNode.IsDescendantOf(@CurrentEmployee) = 1;
```
Lista wyników zawiera zarówno Sariya, jak i Wanida. Sariya jest wymieniona, ponieważ ta wartość jest potomkiem na poziomie 0. Wanida jest potomkiem na poziomie 1.
Możesz również wykonać zapytanie dotyczące tych informacji przy użyciu metody GetAncestor (aparatu bazy danych). GetAncestor przyjmuje argument dla poziomu, który chcesz zwrócić. Ponieważ Wanida jest jednym poziomem poniżej Sariya, użyj GetAncestor(1), jak pokazano w poniższym kodzie:
```
DECLARE @CurrentEmployee AS hierarchyid;

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

SELECT OrgNode.ToString() AS Text_OrgNode,
       *
FROM HumanResources.EmployeeOrg
WHERE OrgNode.GetAncestor(1) = @CurrentEmployee;
```
Tym razem wynik pokazuje tylko Wanidę.

Teraz zmień @CurrentEmployee na David (EmployeeID 6) i poziom na 2. Wykonaj następujące polecenie, aby również zwrócić Wanidę:

DECLARE @CurrentEmployee AS hierarchyid;

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

SELECT OrgNode.ToString() AS Text_OrgNode,
       *
FROM HumanResources.EmployeeOrg
WHERE OrgNode.GetAncestor(2) = @CurrentEmployee;

Tym razem przyjmujesz także Maryję, która raportuje również do Davida, o dwa szczeble niżej.

Użyj GetRoot i GetLevel

W miarę wzrostu hierarchii trudniej jest określić, gdzie znajdują się członkowie w hierarchii. Użyj metody GetLevel (aparatu bazy danych), aby dowiedzieć się, ile poziomów w dół każdego wiersza znajduje się w hierarchii. Wykonaj następujący kod, aby wyświetlić poziomy wszystkich wierszy:
```
SELECT OrgNode.ToString() AS Text_OrgNode,
       OrgNode.GetLevel() AS EmpLevel,
       *
FROM HumanResources.EmployeeOrg;
```
Użyj metody GetRoot (aparatu bazy danych), aby znaleźć węzeł główny w hierarchii. Poniższy kod zwraca pojedynczy wiersz, który jest korzeniem.
```
SELECT OrgNode.ToString() AS Text_OrgNode,
       *
FROM HumanResources.EmployeeOrg
WHERE OrgNode = hierarchyid::GetRoot();
```

Zmiana kolejności danych w tabeli hierarchicznej przy użyciu metod hierarchicznych

Dotyczy: SQL Server

Reorganizacja hierarchii jest typowym zadaniem konserwacji. W tym zadaniu użyjemy instrukcji UPDATE z metodą GetReparentedValue (Database Engine), aby najpierw przenieść pojedynczy wiersz do nowej lokalizacji w hierarchii. Następnie przenosimy całe poddrzewo do nowej lokalizacji.

Metoda GetReparentedValue przyjmuje dwa argumenty. Pierwszy argument opisuje część hierarchii, która ma zostać zmodyfikowana. Jeśli na przykład hierarchia jest /1/4/2/3/ i chcesz zmienić sekcję /1/4/, hierarchia stanie się /2/1/2/3/, pozostawiając dwa ostatnie węzły (2/3/) bez zmian, należy podać węzły zmieniające (/1/4/) jako pierwszy argument. Drugi argument zawiera nowy poziom hierarchii, w naszym przykładzie /2/1/. Dwa argumenty nie muszą zawierać tej samej liczby poziomów.

Przenieś pojedynczy wiersz do nowej lokalizacji w hierarchii

Obecnie Wanida raportuje do Sarii. W tej procedurze przeniesiesz Wanida z bieżącego węzła /1/1/, aby ta osoba zgłaszała się do Jill. Nowy węzeł staje się /3/1/ więc /1/ jest pierwszym argumentem i /3/ jest drugim. Odpowiadają one wartościom OrgNode Sariya i Jill. Wykonaj następujący kod, aby przenieść firmę Wanida z organizacji Sariya do programu Jill:

DECLARE @CurrentEmployee AS hierarchyid,
        @OldParent AS hierarchyid,
        @NewParent AS 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;

Wykonaj następujący kod, aby zobaczyć wynik:

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

Wanida jest teraz w węźle /3/1/.

Reorganizacja sekcji hierarchii

Aby zademonstrować, jak przenieść większą liczbę osób jednocześnie, najpierw wykonaj następujący kod, aby dodać stażystę raportującego do Wanida.
```
EXECUTE AddEmp 269, 291, 'Kevin', 'Marketing Intern';
```

Teraz Kevin raportuje do Wanidy, która raportuje do Jill, która raportuje do Davida. Oznacza to, że Kevin jest na poziomie /3/1/1/. Aby przenieść wszystkich podwładnych Jill do nowego menedżera, zmieniamy wartość wszystkich węzłów, które mają /3/ jako ich OrgNode, na nową. Wykonaj następujący kod, aby zaktualizować, żeby Wanida raportowała do Sariya, ale żeby Kevin nadal raportował do Wanidy.

DECLARE @OldParent AS hierarchyid,
        @NewParent AS 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 AS hierarchyid;

OPEN children_cursor;

FETCH NEXT FROM children_cursor INTO @ChildId;

WHILE @@FETCH_STATUS = 0
    BEGIN
        START:
        DECLARE @NewId AS 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;

Wykonaj następujący kod, aby zobaczyć wynik:

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

Oto zestaw wyników.

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

Całe drzewo organizacyjne, które podlegało Jill (zarówno Wanida, jak i Kevin), teraz raportuje do Sariya.

Aby uzyskać procedurę składowaną do reorganizacji części hierarchii, zobacz sekcję Move subtrees oraz sekcję Hierarchical data (SQL Server).

Opinia

Czy ta strona była pomocna?

Last updated on 2026-01-05

Lekcja 2. Tworzenie danych i zarządzanie nimi w tabeli hierarchicznej

Prerequisites

Tworzenie tabeli przy użyciu typu danych hierarchyid

Tworzenie tabeli EmployeeOrg

Wypełnianie tabeli hierarchicznej przy użyciu metod hierarchicznych

Wstaw korzeń drzewa hierarchii

Wstawianie pracownika podrzędnego

Tworzenie procedury wprowadzania nowych węzłów

Wykonywanie zapytań względem tabeli hierarchicznej przy użyciu metod hierarchii

Znajdowanie węzłów podrzędnych

Użyj GetRoot i GetLevel

Zmiana kolejności danych w tabeli hierarchicznej przy użyciu metod hierarchicznych

Przenieś pojedynczy wiersz do nowej lokalizacji w hierarchii

Reorganizacja sekcji hierarchii

Opinia

Dodatkowe zasoby