Riordinamento di dati in una tabella gerarchica utilizzando metodi gerarchici

La riorganizzazione di una gerarchia è un'attività di manutenzione comune. In questa attività verrà utilizzata 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 diviene /2/1/2/3/, mentre gli ultimi due nodi (2/3 /) restano immutati ed è necessario fornire i nodi modificati (/1/4/) come primo argomento. Nel secondo argomento viene fornito il nuovo livello della gerarchia, nell'esempio /2/1/. Non è necessario che i due argomenti contengano lo stesso numero di livelli.

Per spostare una sola riga in un percorso nuovo nella gerarchia

1. 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 diverrà /3/1/, /1/ diventa il primo argomento e /3/ il secondo. Corrispondono ai valori di valuta di 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
   

2. 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/.

Per riorganizzare una sezione di una gerarchia

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

2. 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;
   

3. Eseguire il codice seguente per visualizzare il risultato:

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

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//2      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" in Utilizzo di dati hierarchyid.

Attività successiva della lezione

Riepilogo: Gestione di dati in una tabella gerarchica