Partilhar via

  • Artigo

Reordenando dados em tabela hierárquica usando métodos hierárquicos

Reorganizar uma hierarquia é uma tarefa de manutenção comum. Nesta tarefa, usaremos a instrução UPDATE com o método GetReparentedValue para mover primeiramente uma única linha para um novo local da hierarquia. Em seguida, moveremos uma subárvore inteira para um novo local.

O método GetReparentedValue toma dois argumentos. O primeiro argumento descreve a parte da hierarquia a ser modificada. Por exemplo, se uma hierarquia for /1/4/2/3/ e você deseja alterar a seção /1/4/ , a hierarquia se torna /2/1/2/3/; deixando os últimos dois nós (2/3 /) inalterados, você precisará fornecer os nós que estão sendo alterados (/1/4/) como primeiro argumento. O segundo argumento fornece o novo nível hierárquico do nosso exemplo /2/1/. Os dois argumentos não precisam conter o mesmo número de níveis.

Para mover uma linha única para um novo local hierárquico

  1. Atualmente, Wanida reporta-se a Sariya. Neste procedimento, você move Wanida de seu nó /1/1/ atual, de modo que ela se reporte a Jill. Seu novo nó se tornará /3/1/ e, dessa forma, /1/ será o primeiro argumento e /3/ o segundo. Esses correspondem aos valores OrgNode de Sariya e Jill. Execute o código a seguir para mover Wanida da organização de Sariya para a organização de 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. Execute o código a seguir e observe o resultado:

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

    Wanida encontra-se agora no nó /3/1/.

Para reorganizar uma seção de hierarquia

  1. Para demonstrar como mover um grande número de pessoas ao mesmo tempo, execute primeiramente o código a seguir para adicionar um estagiário se reportando a Wanida:

    EXEC AddEmp 269, 291, 'Kevin', 'Marketing Intern'  ;
GO

  2. Kevin agora se reporta a Wanida, que se reporta a Jill, que se reporta a David. Isso significa que Kevin se encontra no nível /3/1/1/. Para mover todos os subordinados de Jill para um novo administrador, atualizaremos todos os nós com /3/ como seus OrgNode para um novo valor. Execute o código a seguir para atualizar Wanida de modo que ela se torne subordinada à Sariya, mas deixando Kevin subordinado à 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. Execute o seguinte código para ver o resultado:

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

Conjunto de resultados.

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

Toda a árvore organizacional que se reportava a Jill (Wanida e Kevin), agora se reporta a Sariya.

Para ver um procedimento armazenado que reorganiza uma seção de uma hierarquia, consulte a seção “Movendo subárvores” de Trabalhando com dados hierarchyid.