2. lecke: Adatok létrehozása és kezelése hierarchikus táblázatban

2025-02-04

A következőkre vonatkozik:SQL Server Azure SQL Database Azure SQL Felügyelt példány SQL-adatbázis a Microsoft Fabric platformon

Az 1. leckében módosított egy meglévő táblát a hierarchiaazonosító adattípus használatára, és feltöltötte a hierarchiaazonosító oszlopot a meglévő adatok megjelenítésével. Ebben a leckében egy új táblával kezd, és hierarchikus módszerekkel szúr be adatokat. Ezután a hierarchikus módszerekkel lekérdezheti és módosíthatja az adatokat.

Előfeltételek

Az oktatóanyag elvégzéséhez az SQL Server Management Studióra, az SQL Servert futtató kiszolgálóhoz való hozzáférésre és egy AdventureWorks2022-adatbázisra van szüksége.

Telepítse SQL Server Management Studio (SSMS).
Telepítse SQL Server 2022 Developer Edition.
Töltse le AdventureWorks-mintaadatbázis.

Az adatbázisok SSMS-ben való visszaállítására vonatkozó utasítások a következők: Adatbázis biztonsági mentésének visszaállítása SSMS-használatával.

Tábla létrehozása hierarchiaazonosító adattípussal

Az alábbi példa egy EmployeeOrgnevű táblát hoz létre, amely az alkalmazottak adatait és a jelentési hierarchiát tartalmazza. A példa létrehozza a táblát a AdventureWorks2022 adatbázisban, de ez nem kötelező. A példa egyszerűségéhez ez a táblázat csak öt oszlopot tartalmaz:

OrgNode egy hierarchiaazonosító oszlop, amely a hierarchikus kapcsolatot tárolja.
OrgLevel egy számított oszlop, amely az egyes csomópontszinteket a hierarchiában tároló OrgNode oszlopon alapul. Szélességi kereséses indexhez használják.
EmployeeID az alkalmazásokhoz, például a bérszámfejtéshez használt tipikus alkalmazotti azonosítószámot tartalmazza. Az új alkalmazásfejlesztés során az alkalmazások használhatják a OrgNode oszlopot, és nincs szükség erre a különálló EmployeeID oszlopra.
EmpName az alkalmazott nevét tartalmazza.
Title tartalmazza az alkalmazott címét.

Az EmployeeOrg tábla létrehozása

A Lekérdezésszerkesztő ablakában futtassa a következő kódot a EmployeeOrg tábla létrehozásához. Amikor a OrgNode oszlopot fürtözött indexű elsődleges kulcsként adja meg, egy mélységi index jön létre:

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

Futtassa a következő kódot egy összetett index létrehozásához a OrgLevel és OrgNode oszlopokon a hatékony első szintű keresések támogatásához:
```
CREATE UNIQUE INDEX EmployeeOrgNc1
ON HumanResources.EmployeeOrg(OrgLevel, OrgNode);
GO
```

A tábla készen áll az adatokra. A következő feladat hierarchikus módszerekkel tölti fel a táblát.

Hierarchikus tábla feltöltése hierarchikus módszerekkel

AdventureWorks2022 nyolc alkalmazottja van a Marketing osztályon. Az alkalmazotti hierarchia a következőképpen néz ki:

David, EmployeeID 6, a marketing menedzser. Három marketingszakértő tartozik a Davidalá.

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

A Marketing Assistant Wanida (EmployeeID 269) jelentései a Sariya-höz, és a Marketing Assistant Mary (EmployeeID 272) a John-höz jelent.

A hierarchiafa gyökerének beszúrása

Az alábbi példa beszúrja David a Marketing Managert a hierarchia gyökerénél található táblába. A OrdLevel oszlop egy számított oszlop. Ezért ez nem része a INSERT utasításnak. Az első rekord a GetRoot (adatbázismotor) metódussal töltődik fel, és a hierarchia gyökereként szolgál.
```
INSERT HumanResources.EmployeeOrg (OrgNode, EmployeeID, EmpName, Title)
VALUES (hierarchyid::GetRoot(), 6, 'David', 'Marketing Manager');
GO
```

Hajtsa végre a következő kódot a tábla kezdeti sorának vizsgálatához:

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

Itt van az eredményhalmaz.

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

Az előző leckéhez hasonlóan a ToString() metódussal a hierarchiaazonosítót adattípust könnyebben érthető formátummá alakítjuk.

Alárendelt alkalmazott beszúrása

Sariya bejelentésre kerül David-hez. Sariya's csomópont beszúrásához létre kell hoznia egy megfelelő OrgNode adattípusú értéket hierarchiaazonosító. Az alábbi kód létrehoz egy hierarchyid adattípusú változót, és feltölti azt a tábla gyökér OrgNode értékével. Ezután ezt a változót használja a GetDescendant (adatbázismotor) metódussal egy alárendelt csomópontot tartalmazó sor beszúrásához. GetDescendant két argumentumot vesz fel. Tekintse át az argumentumértékek alábbi beállításait:
- Ha a szülő NULL, akkor GetDescendant a NULLértéket adja vissza.
- Ha a szülő nem NULL, és mind child1, mind child2NULL, akkor GetDescendant a szülő egy gyermekét adja vissza.
- Ha a szülő és a child1 nem NULL, és child2NULL, GetDescendant a child1-nél nagyobb szülő gyermekét adja vissza.
- Ha a szülő és a child2 nem NULL, és child1NULL, akkor GetDescendant egy olyan gyermeket ad vissza a szülőtől, amely kisebb, mint child2.
- Ha a szülő, a child1és a child2 nem NULL, GetDescendant a child1 és a child2alatti szülő gyermekét adja vissza.
Az alábbi kód a gyökér szülőjének (NULL, NULL) argumentumait használja, mert a gyökéren kívül még nincsenek sorok a táblában. Hajtsa végre a következő kódot Sariyabeszúrásához:
```
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');
```

Ismételje meg a lekérdezést az első eljárásból a tábla lekérdezéséhez, és nézze meg, hogyan jelennek meg a bejegyzések:

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

Itt vannak az eredmények.

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

Új csomópontok beírására szolgáló eljárás létrehozása

Az adatok beírásának egyszerűsítése érdekében hozza létre a következő tárolt eljárást, amellyel alkalmazottakat vehet fel a EmployeeOrg táblába. Az eljárás elfogadja a hozzáadott alkalmazott bemeneti értékeit. Ez magában foglalja az új alkalmazott kezelőjének EmployeeID, az új alkalmazott EmployeeID számát, valamint a vezetéknevét és a címét. Az eljárás GetDescendant() és a GetAncestor (Adatbázismotor) metódust is használja. Hajtsa végre a következő kódot az eljárás létrehozásához:

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

Az alábbi példa hozzáadja a maradék négy alkalmazottat, akik közvetlenül vagy közvetve a Davidalá tartoznak.

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

Ismét futtassa a következő lekérdezést, majd vizsgálja meg a EmployeeOrg tábla sorait:

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

Itt van az eredményhalmaz.

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

A táblázat már teljes egészében ki van töltve a marketingszervezet adataival.

Hierarchikus tábla lekérdezése hierarchikus metódusokkal

Most, hogy a HumanResources.EmployeeOrg tábla teljesen ki van töltve, ez a feladat bemutatja, hogyan kérdezheti le a hierarchiát néhány hierarchikus metódus használatával.

Alárendelt csomópontok keresése

Sariya-nak van egy alárendelt alkalmazottja. Sariya alárendeltjeinek lekérdezéséhez hajtsa végre a következő lekérdezést, amely az IsDescendantOf (Adatbázismotor) metódust használja:
```
DECLARE @CurrentEmployee HIERARCHYID

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

SELECT *
FROM HumanResources.EmployeeOrg
WHERE OrgNode.IsDescendantOf(@CurrentEmployee ) = 1;
```
Az eredmény felsorolja Sariya és Wanidais. Sariya azért van felsorolva, mert ez az érték az 0 szinten lévő leszármazott. Wanida a 1 szinten leszármazott.
Ezeket az információkat a GetAncestor (adatbázismotor) metódussal is lekérdezheti. GetAncestor a visszaadni kívánt szint argumentumát veszi figyelembe. Mivel Wanida egy szinttel Sariya alatt van, használja a GetAncestor(1)-t, ahogy azt a következő kód mutatja:
```
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
```
Az eredmény ezúttal csak Wanida-t sorolja fel.

Most változtassa meg a @CurrentEmployee-t David (EmployeeID 6)-re, és a szintet 2-re. Hajtsa végre a következő lépéseket Wanida visszaadásához:

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

Ezúttal Maryt is megkapja, aki szintén beszámol Davidnek, két szinttel lejjebb.

A GetRoot és a GetLevel használata

A hierarchia növekedésével nehezebb meghatározni, hogy a tagok hol vannak a hierarchiában. A GetLevel (adatbázismotor) metódussal megállapíthatja, hogy az egyes sorok hány szintje található a hierarchiában. Hajtsa végre a következő kódot az összes sor szintjeinek megtekintéséhez:
```
SELECT OrgNode.ToString() AS Text_OrgNode,
OrgNode.GetLevel() AS EmpLevel, *
FROM HumanResources.EmployeeOrg;
GO
```
A GetRoot (adatbázismotor) metódussal keresse meg a gyökércsomópontot a hierarchiában. A következő kód az egyetlen sort adja vissza, amely a gyökér:
```
SELECT OrgNode.ToString() AS Text_OrgNode, *
FROM HumanResources.EmployeeOrg
WHERE OrgNode = HIERARCHYID::GetRoot();
GO
```

Adatok átrendezása hierarchikus táblákban hierarchikus módszerekkel

A következőkre vonatkozik: SQL Server

A hierarchia átrendezése gyakori karbantartási feladat. Ebben a feladatban egy UPDATE utasítást használunk a GetReparentedValue (adatbázismotor) metódussal, hogy először egyetlen sort helyezzünk át egy új helyre a hierarchiában. Ezután áthelyezünk egy teljes részfát egy új helyre.

A GetReparentedValue metódus két argumentumot vesz fel. Az első argumentum a módosítani kívánt hierarchiarészt írja le. Ha például egy hierarchia /1/4/2/3/, és módosítani szeretné a /1/4/ szakaszt, a hierarchia /2/1/2/3/lesz, és az utolsó két csomópont (2/3/) változatlan marad, első argumentumként meg kell adnia a változó csomópontokat (/1/4/). A második argumentum az új hierarchiaszintet adja meg a példánkban /2/1/. A két argumentumnak nem kell azonos számú szintet tartalmaznia.

Egyetlen sor áthelyezése a hierarchia új helyére

Wanida jelenleg Sariya-nak jelentkezik. Ebben az eljárásban áthelyezi Wanidát az aktuális csomópontról /1/1/, hogy ez a személy jelentsen Jillnek. Az új csomópont /3/1/ lesz, így /1/ az első argumentum, /3/ pedig a második. Ezek megfelelnek Sariya és Jill OrgNode értékeinek. Hajtsa végre a következő kódot Wanida áthelyezéséhez Sariya szervezetéből Jill's-be:

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

Hajtsa végre a következő kódot az eredmény megtekintéséhez:

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

Wanida most a /3/1/csomópontnál van.

Hierarchia egy szakaszának átrendezése

Ha be szeretné mutatni, hogyan helyezhet át egyszerre nagyobb számú embert, először hajtsa végre a következő kódot egy gyakornoki jelentés Wanida-hoz való hozzáadásához:
```
EXEC AddEmp 269, 291, 'Kevin', 'Marketing Intern';
GO
```

Kevin most Wanidának tesz jelentést, aki Jillnek, aki Davidnek tesz jelentést. Ez azt jelenti, hogy Kevin a(z) /3/1/1/szinten van. Jill összes alárendeltjét egy új vezetőhöz helyezzük át, az összes olyan csomópontot frissítjük, amelynek /3/ eleme a OrgNode, egy új értékre. Hajtsa végre a következő kódot, hogy frissítse Wanida-t, hogy jelentést jelentsen Sariya-nak, de kevin továbbra is jelentést küld Wanida-nak:

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;