Novos tipos de dados do SQL 2008: HIERARCHYID (Parte 2)

Depois de um logo período sem publicar nada no Blog (fiquei enrolado no trabalho...), finalmente vou finalizar o post sobre o tipo de dados hierárquico. A primeira parte está no endereço: http://sqlserver-brasil.blogspot.com/2008/05/novos-tipos-de-dados-do-sql-2008_14.html.

No último post mostrei como implementar hierarquia no SQL Server 2005, veremos agora as novidades do SQL Server 2008. Vou utilizar a mesma hierarquia do post anterior, segue abaixo a figura:


O novo tipo de dados hierarchyid representa uma hierarquia, estando disponível uma série de métodos para sua manipulação: GetAncestor, GetDescendant, GetLevel, GetRoot, IsDescendant, Parse, Read, Reparent, ToString, Write. Vou utilizar alguns destes no exemplo abaixo.

Primeiro vamos criar a tabela abaixo contendo o novo tipo de dados hierarchyid:

CREATE TABLE FuncionarioHierarchies(
FuncionarioID int not null primary key,
Nome varchar(10) not null,
Cargo varchar(12) not null,
Hierarquia hierarchyid null)

Para incluir o ROOT da hierarquia (Presidente Jose) vamos utilizar o método GetRoot, que retorno o ROOT da hierarquia:

INSERT FuncionarioHierarchies VALUES
(1,'Jose','Presidente',hierarchyid::GetRoot())

SELECT * FROM FuncionarioHierarchies
where Hierarquia = hierarchyid::GetRoot()

Reparem que para consultar o ROOT, basta utilizar o mesmo método da inclusão!

Vamos incluir agora os Diretores Maria e Pedro, utilizando o método GetDescendant:

-- Inclui Maria
DECLARE @Pai hierarchyid
SELECT @Pai = hierarchyid::GetRoot() FROM FuncionarioHierarchies
INSERT FuncionarioHierarchies VALUES
(2,'Maria','Diretor',@Pai.GetDescendant(NULL,NULL))

-- Inclui Pedro
DECLARE @Pai hierarchyid
DECLARE @PrimeiroFilho hierarchyid
SELECT @Pai = hierarchyid::GetRoot() FROM FuncionarioHierarchies
SELECT @PrimeiroFilho =@Pai.GetDescendant(NULL,NULL)
INSERT FuncionarioHierarchies VALUES
(3,'Pedro','Diretor',@Pai.GetDescendant(@PrimeiroFilho,NULL))

A tabela abaixo mostra como utilizar os dois parâmetros do método pai.GetDescendant (filho1,filho2):



Para completar nosso organograma utilize o script abaixo:

DECLARE @Pai hierarchyid
DECLARE @PrimeiroFilho hierarchyid
SELECT @Pai = Hierarquia FROM FuncionarioHierarchies
where FuncionarioID = 2
INSERT FuncionarioHierarchies VALUES
(4,'Ana','Gerente',@Pai.GetDescendant(NULL,NULL))
SELECT @PrimeiroFilho =@Pai.GetDescendant(NULL,NULL)
INSERT FuncionarioHierarchies VALUES
(5,'Lucia','Gerente',@Pai.GetDescendant(@PrimeiroFilho,NULL))

SELECT @Pai = Hierarquia FROM FuncionarioHierarchies where FuncionarioID = 3
INSERT FuncionarioHierarchies VALUES
(6,'Ronaldo','Gerente',@Pai.GetDescendant(NULL,NULL))

SELECT @Pai = Hierarquia FROM FuncionarioHierarchies where FuncionarioID = 5
INSERT FuncionarioHierarchies VALUES
(7,'Marcio','Coordenador',@Pai.GetDescendant(NULL,NULL))
go

Agora vamos retornar a hierarquia utilizando o SELECT abaixo com o método GetAncestor:

SELECT FuncionarioID,Nome,Cargo,Hierarquia.GetLevel() as Nivel,
(Select FuncionarioID FROM FuncionarioHierarchies
Where Hierarquia = e.Hierarquia.GetAncestor(1)) As Chefe
FROM FuncionarioHierarchies e

Até o próximo post,
Landry.