Lezione 2: Creare e gestire dati in una tabella gerarchica

Articolo
04/26/2023

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 AdventureWorks.

Installare SQL Server Management Studio.
Installare SQL Server 2017 Developer Edition.
Scaricare un database campione AdventureWorks.

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

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 8 dipendenti che lavorano nel reparto Marketing. La gerarchia dei dipendenti è simile alla seguente:

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

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

Wanida , Marketing Assistant (EmployeeID 269), fa riferimento a Sariya, mentre Mary Marketing Assistant (EmployeeID 272), fa riferimento a John.

Inserire la radice dell'albero gerarchico

Nell'esempio seguente David , il responsabile marketing, è inserito nella tabella alla radice della gerarchia. La colonna OrdLevel è una colonna calcolata. Pertanto, non è parte dell'istruzione INSERT. Questo primo record usa il metodo GetRoot() per popolarsi come radice 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 fa riferimento a David. Per inserire il nodo di Sariya , è 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() per inserire la riga che è un nodo subordinato. GetDescendant accetta due argomenti. Rivedere le opzioni seguenti per i valori dell'argomento:
- Se il padre è NULL, GetDescendant restituisce NULL.
- Se il padre non è NULL e child1 e child2 sono NULL, GetDescendant restituisce un figlio del padre.
- Se il padre e child1 non sono NULL e child2 è NULL, GetDescendant restituisce un figlio del padre maggiore di child1.
- Se il padre e child2 non sono NULL e child1 è NULL, GetDescendant restituisce un figlio del padre minore di child2.
- Se il padre, child1 e child2 non sono NULL, GetDescendant restituisce un figlio del padre maggiore di child1 e uno minore di child2.
Il codice seguente utilizza gli argomenti (NULL, NULL) dell'elemento padre radice perché nella tabella non esiste ancora alcuna riga, ad eccezione della radice. 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() . Eseguire il codice seguente per creare la procedura:

CREATE PROC 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 4 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 :
```
DECLARE @CurrentEmployee hierarchyid  

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

SELECT *  
FROM HumanResources.EmployeeOrg  
WHERE OrgNode.IsDescendantOf(@CurrentEmployee ) = 1 ;  
```
I risultati indicano sia Sariya sia 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 . 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 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 per cercare il nodo radice della gerarchia. Il codice seguente restituisce la singola riga che è la radice:

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 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, è 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 suo nodo nuovo diventerà /3/1/ e /1/ diventa il primo argomento, mentre /3/ diventa il secondo argomento. 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/ in OrgNode con 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 riorganizzare una sezione di una gerarchia tramite una stored procedure, vedere la sezione Spostamento di sottoalberi.

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

Commenti e suggerimenti

Risorse aggiuntive