Lezione 2: Creare e gestire dati in una tabella gerarchica
Si applica a:SQL ServerDatabase SQL di AzureIstanza 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 colonnaOrgNode
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
eOrgNode
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') ;
- Se il padre è NULL,
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, utilizzareGetAncestor(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.
Commenti e suggerimenti
https://aka.ms/ContentUserFeedback.
Presto disponibile: Nel corso del 2024 verranno gradualmente disattivati i problemi di GitHub come meccanismo di feedback per il contenuto e ciò verrà sostituito con un nuovo sistema di feedback. Per altre informazioni, vedereInvia e visualizza il feedback per