Lição 2: Criar e gerenciar dados em uma tabela hierárquica

Artigo
04/26/2023

Aplica-se a:SQL Server Banco de Dados SQL do Azure Instância Gerenciada de SQL do Azure

Na Lição 1, você modificou uma tabela existente para usar o tipo de dados hierarchyid e populou a coluna hierarchyid com a representação dos dados existentes. Nesta lição, você iniciará com uma tabela nova, e inserindo dados usando os métodos hierárquicos. Em seguida, você vai consultar e manipular os dados usando os métodos hierárquicos.

Pré-requisitos

Para concluir este tutorial, você precisará do SQL Server Management Studio, bem como acesso a um servidor que executa o SQL Server e um banco de dados do AdventureWorks.

Instalar o SQL Server Management Studio.
Instalar o SQL Server 2017 Developer Edition.
Baixar Bancos de dados de exemplo do AdventureWorks.

Instruções para restaurar bancos de dados no SSMS são encontradas aqui: Restaurar um banco de dados.

Criar uma tabela por meio de um tipo de dados hierarchyid

O exemplo a seguir cria uma tabela com o nome EmployeeOrg, que inclui dados de funcionário junto com sua hierarquia de relatórios. O exemplo cria a tabela no banco de dados AdventureWorks2022 , mas isso é opcional. Para manter o exemplo simples, essa tabela inclui somente cinco colunas:

OrgNode é uma coluna hierarchyid que armazena a relação hierárquica.
OrgLevel é uma coluna computada, com base na coluna OrgNode, que armazena cada nível de nó na hierarquia. Ela será usada para um índice por amplitude.
EmployeeID contém o número de identificação de funcionário comum usado para aplicativos como folha de pagamento. No desenvolvimento de um novo aplicativo, os aplicativos podem usar a coluna OrgNode e a coluna EmployeeID separada não é necessária.
EmpName contém o nome do funcionário.
Title contém o cargo do funcionário.

Criar a tabela EmployeeOrg

Na janela do Editor de Consultas, execute a consulta a seguir para criar a tabela chamada EmployeeOrg. A especificação da coluna OrgNode como a chave primária com um índice clusterizado criará um índice por amplitude:

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

Execute o seguinte código para criar um índice composto nas colunas OrgLevel e OrgNode para oferecer suporte a pesquisas eficientes por amplitude:
```
CREATE UNIQUE INDEX EmployeeOrgNc1   
ON HumanResources.EmployeeOrg(OrgLevel, OrgNode) ;  
GO  
```

Agora a tabela está pronta para receber dados. A próxima tarefa populará a tabela usando métodos hierárquicos.

Preencher uma tabela hierárquica utilizando métodos hierárquicos

AdventureWorks2022 tem 8 funcionários trabalhando no departamento de Marketing. A hierarquia dos funcionários é assim:

Davi, EmployeeID 6, é o Gerente de Marketing. Três especialistas em Marketing são subordinados a Davi:

Sara, EmployeeID 46
Bruno, EmployeeID 271
Julieta, EmployeeID 119

A Assistente de Marketing Valentina , (EmployeeID 269), é subordinada a Sara, e a Assistente de Marketing Marina , (EmployeeID 272), é subordinada a Bruno.

Inserir a raiz da árvore hierárquica

O exemplo a seguir insere Davi , o Gerente de Marketing, na tabela da raiz da hierarquia. A coluna OrdLevel é uma coluna calculada. Portanto, não faz parte da instrução INSERT. Este primeiro registro usa o método GetRoot() para popular o primeiro registro como a raiz da hierarquia.
```
INSERT HumanResources.EmployeeOrg (OrgNode, EmployeeID, EmpName, Title)  
VALUES (hierarchyid::GetRoot(), 6, 'David', 'Marketing Manager') ;  
GO  
```

Execute o seguinte código para examinar a linha inicial na tabela:

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

Este é o conjunto de resultados.

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

Como na lição anterior, usamos o método ToString() para converter o tipo de dados hierarchyid em um formato mais fácil de entender.

Inserir um funcionário subordinado

Sara é subordinada a Davi. Para inserir o nó de Sara , você deve criar um valor OrgNode apropriado do tipo de dados hierarchyid. O código a seguir cria uma variável do tipo de dados hierarchyid e a popula com o valor OrgNode de raiz da tabela. Em seguida, usa essa variável com o método GetDescendant() para inserir a linha que é um nó subordinado. GetDescendant usa dois argumentos: Analise as seguintes opções para os valores de argumento:
- Se o pai for o NULL, GetDescendant retornará NULL.
- Se o pai não for NULL, e child1 e child2 forem NULL, GetDescendant retornará um filho do pai.
- Se o pai e child1 não forem NULL e child2 for NULL, GetDescendant retornará um filho do pai maior que child1.
- Se o pai e child2 não forem NULL e child1 for NULL, GetDescendant retornará um filho do pai menor que child2.
- Se parent, child1 e child2 não forem NULL, GetDescendant retornará um filho do pai maior que child1 e menor que child2.
O código a seguir usa os argumentos (NULL, NULL) do pai da raiz, pois ainda não há linhas na tabela exceto a raiz. Execute o seguinte código para inserir Sara:
```
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') ;  
```

Repita a consulta do primeiro procedimento para consultar a tabela e verificar como as entradas são exibidas:

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

Este é o conjunto de resultados.

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

Criar um procedimento para inserir novos nós

Para simplificar a inserção de dados, crie o procedimento armazenado a seguir para adicionar funcionários à tabela EmployeeOrg . O procedimento aceita valores de entrada sobre o empregado sendo adicionado. Isso inclui o EmployeeID do gerente do novo funcionário, o número EmployeeID do novo funcionário, além de seu nome e cargo. O procedimento usa GetDescendant() e o método GetAncestor() . Execute o código a seguir para criar o procedimento:

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

O exemplo a seguir adiciona os quatro funcionários restantes que são subordinados direta ou indiretamente a Davi.

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

Novamente, execute a seguinte consulta para examinar as linhas na tabela EmployeeOrg :

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

Este é o 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  
/2/          0x68    1        271        John    Marketing Specialist  
/2/1/        0x6AC0  2        272        Mary    Marketing Assistant  
/3/          0x78    1        119        Jill    Marketing Specialist

Agora a tabela está totalmente populada com a organização de marketing.

Consultar uma tabela hierárquica por meio de métodos de hierarquia

Agora que a tabela HumanResources.EmployeeOrg está completamente populada, essa tarefa mostrará como consultar a hierarquia usando alguns métodos hierárquicos.

Localizar nós subordinados

Sariya tem um funcionário subordinado. Para consultar os subordinados de Sara, execute a seguinte consulta, que usa o método IsDescendantOf :
```
DECLARE @CurrentEmployee hierarchyid  

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

SELECT *  
FROM HumanResources.EmployeeOrg  
WHERE OrgNode.IsDescendantOf(@CurrentEmployee ) = 1 ;  
```
O resultado lista Sariya e Wanida. Sariya é listada porque é a descendente no nível 0. Wanida é a descendente no nível 1.
Você também pode consultar essas informações usando o método GetAncestor . GetAncestor usa um argumento para o nível que você está tentando retornar. Como Wanida está um nível abaixo de Sariya, use GetAncestor(1) conforme demonstrado no seguinte código:
```
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  
```
Desta vez o resultado lista apenas Wanida.

Agora altere o @CurrentEmployee para David (EmployeeID 6) e o nível para 2. Execute o seguinte para retornar também 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

Desta vez, você também recebe Mary que também é subordinada a David, dois níveis abaixo.

Usar GetRoot e GetLevel

À medida que a hierarquia fica maior é mais difícil determinar onde os membros estão na hierarquia. Use o método GetLevel para localizar quantos níveis abaixo cada linha está na hierarquia. Execute o seguinte código para exibir os níveis de todas as linhas:
```
SELECT OrgNode.ToString() AS Text_OrgNode,   
OrgNode.GetLevel() AS EmpLevel, *  
FROM HumanResources.EmployeeOrg ;  
GO  
```

Use o método GetRoot para localizar o nó raiz na hierarquia. O seguinte código retorna uma única linha que é a raiz:

SELECT OrgNode.ToString() AS Text_OrgNode, *  
FROM HumanResources.EmployeeOrg  
WHERE OrgNode = hierarchyid::GetRoot() ;  
GO

Reordenar dados em tabela hierárquica por meio de métodos hierárquicos

Aplica-se a:SQL Server

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 usa 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ê desejar alterar a seção /1/4/ , a hierarquia se tornará /2/1/2/3/; se deixar os últimos dois nós (2/3/) inalterados, você precisará fornecer os nós que estão sendo alterados (/1/4/) como o primeiro argumento. O segundo argumento fornece o novo nível hierárquico, em nosso exemplo /2/1/. Os dois argumentos não precisam conter o mesmo número de níveis.

Mover uma linha única para um novo local hierárquico

Atualmente, Wanida reporta-se a Sariya. Neste procedimento, você move Valentina de seu nó /1/1/ atual, de modo que ela se reporte a Julieta. 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 Sara e Julieta. 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

Execute o código a seguir para ver o resultado:

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

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

Reorganizar uma seção de hierarquia

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  
```

Kevin agora se reporta a Wanida, que se reporta a Jill, que se reporta a David. Isso significa que Julio se encontra no nível /3/1/1/. Para mover todos os subordinados de Julieta para um novo administrador, atualizaremos todos os nós com /3/ como seus OrgNode para um novo valor. Execute o seguinte código para atualizar Wanida, de modo que passe a se reportar a Sariya, mas deixando Kevin se reportando 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;

Execute o código a seguir para ver o resultado:

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

Este é o 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/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

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 Movendo subárvores.

Lição 2: Criar e gerenciar dados em uma tabela hierárquica

Pré-requisitos

Criar uma tabela por meio de um tipo de dados hierarchyid

Criar a tabela EmployeeOrg

Preencher uma tabela hierárquica utilizando métodos hierárquicos

Inserir a raiz da árvore hierárquica

Inserir um funcionário subordinado

Criar um procedimento para inserir novos nós

Consultar uma tabela hierárquica por meio de métodos de hierarquia

Localizar nós subordinados

Usar GetRoot e GetLevel

Reordenar dados em tabela hierárquica por meio de métodos hierárquicos

Mover uma linha única para um novo local hierárquico

Reorganizar uma seção de hierarquia

Comentários

Comentários

Recursos adicionais