Lezione 2: Creare e gestire dati in una tabella gerarchica

Articolo
08/01/2024

Si applica a: SQL Server database SQL di Azure Istanza gestita di SQL di Azure

Nella Lezione 1 è stata modificata una tabella esistente per usare il tipo di dati hierarchyid ed è stata popolata la colonna hierarchyid con la rappresentazione dei dati esistenti. In questa lezione, verrà generata una nuova tabella e verranno inseriti i dati utilizzando i metodi gerarchici. Pertanto, verrà eseguita una query e verranno modificati i dati utilizzando i metodi gerarchici.

Prerequisiti

Per completare questa esercitazione, sono necessari SQL Server Management Studio, l'accesso a un server che esegue SQL Server e un database AdventureWorks2022.

Installare SQL Server Management Studio (SSMS).
Installare SQL Server 2022 Developer Edition.
Scaricare un database campione AdventureWorks.

Le istruzioni per il ripristino dei database in SSMS sono disponibili in Ripristinare un backup del database tramite SSMS.

Creare una tabella usando il tipo di dati hierarchyid

Nell'esempio seguente viene creata una tabella denominata EmployeeOrg che include i dati del dipendente e la gerarchia del report. L'esempio crea la tabella nel database AdventureWorks2022 , ma questo è facoltativo. Per mantenere l'esempio semplice, in questa tabella sono incluse solo cinque colonne:

OrgNode è una colonna hierarchyid che archivia la relazione gerarchica.
OrgLevel è una colonna calcolata in base alla colonna OrgNode che archivia il livello di ciascun nodo nella gerarchia. Verrà utilizzata per un indice breadth-first.
Il numero di identificazione tipico del dipendente, utilizzato per applicazioni quali libro paga, è contenuto in EmployeeID. Nello sviluppo di nuove applicazioni, le applicazioni possono utilizzare la colonna OrgNode mentre la colonna separata EmployeeID non è necessaria.
EmpName contiene il nome del dipendente.
Title contiene la posizione del dipendente.

Creare la tabella EmployeeOrg

Nella finestra dell'editor di query eseguire il codice seguente per creare la tabella EmployeeOrg . Specificando la colonna OrgNode come chiave primaria con un indice cluster, verrà creato un indice depth-first:

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

Eseguire il codice riportato di seguito per creare un indice composto per le colonne OrgLevel e OrgNode al fine di supportare ricerche breadth-first efficienti:
```
CREATE UNIQUE INDEX EmployeeOrgNc1
ON HumanResources.EmployeeOrg(OrgLevel, OrgNode);
GO
```

La tabella è ora pronta per i dati. La prossima attività popolerà la tabella utilizzando metodi gerarchici.

Popolare una tabella gerarchica usando metodi gerarchici

AdventureWorks2022 ha otto dipendenti che lavorano nel reparto Marketing. La gerarchia dei dipendenti è simile alla seguente:

David, EmployeeID 6, è il responsabile marketing. Tre marketing specialist riportano a David:

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

L’Assistente marketing Wanida (EmployeeID 269) riporta a Sariya e l’Assistente marketing Mary (EmployeeID 272) riporta a John.

Inserire la radice dell'albero gerarchico

Nell'esempio seguente David, il responsabile marketing, viene inserito nella tabella nell’elemento principale della gerarchia. La colonna OrdLevel è una colonna calcolata. Pertanto, non è parte dell'istruzione INSERT. Questo primo record usa il metodo GetRoot (motore di database) per popolarsi come elemento principale della gerarchia.
```
INSERT HumanResources.EmployeeOrg (OrgNode, EmployeeID, EmpName, Title)
VALUES (hierarchyid::GetRoot(), 6, 'David', 'Marketing Manager');
GO
```

Eseguire il seguente codice per esaminare la riga iniziale della tabella:

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

Questo è il set di risultati.

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

Come illustrato nella lezione precedente, si usa il metodo ToString() per convertire il tipo di dati hierarchyid in un formato più facilmente comprensibile.

Inserire un dipendente subordinato

Sariya riporta a David. Per inserire il nodo di Sariya's, è necessario creare un valore OrgNode appropriato del tipo di dati hierarchyid. Il codice seguente crea una variabile di tipo dati hierarchyid e la popola con il valore OrgNode radice della tabella. A questo punto usa la variabile con il metodo GetDescendant (motore di database) per inserire la riga che è un nodo subordinato. GetDescendant accetta due argomenti. Rivedere le opzioni seguenti per i valori dell'argomento:
- Se l’elemento padre è NULL, GetDescendant restituisce NULL.
- Se l'elemento padre non è NULL e child1 e child2 sono NULL, GetDescendant restituisce un elemento figlio dell'elemento padre.
- Se l’elemento padre e child1 non sono NULL e child2 è NULL, GetDescendant restituisce un elemento figlio dell’elemento padre maggiore di child1.
- Se l’elemento padre e child2 non sono NULL e child1 è NULL, GetDescendant restituisce un elemento figlio dell’elemento padre minore di child2.
- Se l’elemento padre, child1 e child2 non sono NULL, GetDescendant restituisce un elemento figlio dell’elemento padre maggiore di child1 e minore di child2.
Il codice seguente utilizza gli argomenti (NULL, NULL) dell'elemento padre principale perché nella tabella non esiste ancora alcuna riga, ad eccezione dell’elemento principale. Eseguire il codice seguente per inserire 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');
```

Ripetere la query dalla prima procedura per eseguire una query sulla tabella e verificare come appaiono le voci:

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

Questo è il set di risultati.

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

Creare una procedura per l'immissione di nuovi nodi

Per semplificare l'inserimento di dati, creare la stored procedure seguente per aggiungere dipendenti alla tabella EmployeeOrg. La procedura accetta valori di input sul dipendente aggiunto. Include il numero EmployeeID del responsabile del nuovo dipendente, il numero EmployeeID del nuovo dipendente, il nome e il titolo. La procedura usa GetDescendant() e anche il metodo GetAncestor (motore di database). Eseguire il codice seguente per creare la procedura:

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

Nell'esempio seguente vengono aggiunti i quattro dipendenti rimanenti che fanno riferimento direttamente o indirettamente a David.

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

Eseguire nuovamente la query seguente per esaminare le righe della tabella EmployeeOrg:

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

Questo è il set di risultati.

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 tabella ora è popolata completamente con l'organizzazione Marketing.

Eseguire query su una tabella gerarchica usando metodi gerarchici

Ora che la tabella HumanResources.EmployeeOrg è completamente popolata, in questa attività verrà illustrato come eseguire una query sulla gerarchia utilizzando alcuni dei metodi gerarchici.

Trovare nodi subordinati

Sariya ha un dipendente subordinato. Per eseguire una query sui subalterni di Sariya, eseguire la query seguente che usa il metodo IsDescendantOf (motore di database):
```
DECLARE @CurrentEmployee HIERARCHYID

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

SELECT *
FROM HumanResources.EmployeeOrg
WHERE OrgNode.IsDescendantOf(@CurrentEmployee ) = 1;
```
Il risultato riporta sia Sariya che Wanida. Sariya viene indicata perché rappresenta l'elemento discendente al livello 0. Wanida rappresenta l'elemento discendente al livello 1.
È anche possibile eseguire una query per ottenere tali informazioni usando il metodo GetAncestor (motore di database). GetAncestor accetta un argomento per il livello che si tenta di restituire. Poiché Wanida è un livello sotto Sariya, utilizzare GetAncestor(1) come dimostrato nel codice seguente:
```
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
```
Questa volta nei risultati viene indicata solo Wanida.

Ora impostare @CurrentEmployee su David (EmployeeID 6) e il livello su 2. Eseguire quanto segue per restituire anche 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

Questa volta, due livelli più in basso, viene indicata anche Mary che riporta a David.

Usare GetRoot e GetLevel

Con il crescere della gerarchia diventa più difficile determinare la posizione dei membri nella stessa. Usare il metodo GetLevel (motore di database) per scoprire quanti livelli ci sono al di sotto di ogni riga della gerarchia. Eseguire il codice seguente per visualizzare i livelli di tutte le righe:
```
SELECT OrgNode.ToString() AS Text_OrgNode,
OrgNode.GetLevel() AS EmpLevel, *
FROM HumanResources.EmployeeOrg;
GO
```
Usare il metodo GetRoot (motore di database) per cercare il nodo principale della gerarchia. Il codice seguente restituisce la singola riga che è l’elemento principale:
```
SELECT OrgNode.ToString() AS Text_OrgNode, *
FROM HumanResources.EmployeeOrg
WHERE OrgNode = HIERARCHYID::GetRoot();
GO
```

Riordinare dati in una tabella gerarchica usando metodi gerarchici

Si applica a: SQL Server

La riorganizzazione di una gerarchia è un'attività di manutenzione comune. In questa attività verrà usata un'istruzione UPDATE con il metodo GetReparentedValue (motore di database per spostare innanzitutto una singola riga in un percorso nuovo della gerarchia. Verrà quindi spostato un sottoalbero intero in un nuovo percorso.

Il metodo GetReparentedValue utilizza due argomenti. Nel primo argomento viene descritta la parte della gerarchia da modificare. Ad esempio, se una gerarchia è /1/4/2/3/ e si vuole modificare la sezione /1/4/, la gerarchia diventa /2/1/2/3/, lasciando gli ultimi due nodi (2/3/) inalterati, è sarà necessario specificare i nodi modificati (/1/4/) come primo argomento. Il secondo argomento specifica il nuovo livello della gerarchia, nell'esempio /2/1/. Non è necessario che i due argomenti contengano lo stesso numero di livelli.

Spostare una sola riga in un percorso nuovo nella gerarchia

Attualmente Wanida riporta a Sariya. In questa procedura, si sposta Wanida dal nodo corrente /1/1/, in modo che riporti a Jill. Il nuovo nodo diventerà /3/1/ pertanto /1/ diventa il primo argomento e /3/ diventa il secondo. Gli argomenti corrispondono ai valori OrgNode di Sariya e Jill. Eseguire il codice seguente per spostare Wanida dall'organizzazione di Sariya a quella di 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

Eseguire il codice seguente per visualizzare il risultato:

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

Wanida ora è al nodo /3/1/.

Riorganizzare una sezione di una gerarchia

Per dimostrare come spostare contemporaneamente un numero maggiore di persone, eseguire innanzitutto il codice seguente per aggiungere un report del tirocinante a Wanida:
```
EXEC AddEmp 269, 291, 'Kevin', 'Marketing Intern';
GO
```

Ora Kevin riporta a Wanida che riporta a Jill che riporta a David. Questo significa che Kevin è al livello /3/1/1/. Per spostare tutti i subalterni di Jill a un nuovo responsabile, verranno aggiornati tutti i nodi che hanno /3/ come OrgNode in un nuovo valore. Eseguire il codice seguente per aggiornare Wanida in modo che riporti a Sariya, ma lasciando che Kevin riporti a 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;

Eseguire il codice seguente per visualizzare il risultato:

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

Questo è il set di risultati.

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

L'intero albero organizzativo che riportava a Jill (Wanida e Kevin) ora riporta a Sariya.

Per una stored procedure per riorganizzare una sezione di una gerarchia, vedere la sezione Spostare i sottoalberi di Dati gerarchici (SQL Server).

Condividi tramite

Lezione 2: Creare e gestire dati in una tabella gerarchica

Prerequisiti

Creare una tabella usando il tipo di dati hierarchyid

Creare la tabella EmployeeOrg

Popolare una tabella gerarchica usando metodi gerarchici

Inserire la radice dell'albero gerarchico

Inserire un dipendente subordinato

Creare una procedura per l'immissione di nuovi nodi

Eseguire query su una tabella gerarchica usando metodi gerarchici

Trovare nodi subordinati

Usare GetRoot e GetLevel

Riordinare dati in una tabella gerarchica usando metodi gerarchici

Spostare una sola riga in un percorso nuovo nella gerarchia

Riorganizzare una sezione di una gerarchia

Commenti e suggerimenti

Risorse aggiuntive