Lawrence Carvalho (e-mail) é MCP e DBA SQL Server, desenvolvedor em VB, VB .NET, Delphi e Genexus.
Olá pessoal! Antes de tudo, gostaria de dedicar este artigo ao meu grande amigo Harold Barros da SES/MT, pois foi a partir de um problema de lá, que resolvemos juntos, que esse artigo foi escrito.
Neste artigo, será demonstrado como utilizar recursividade para realizar uma seleção de registros e qual é sua principal utilização. Recursividade é a capacidade de um procedimento ou função se chamar várias vezes. A cada chamada do procedimento, novos valores são utilizados com uma área de memória diferente para que não haja interferência entre uma chamada e outra. O grande problema é que uma função recursiva pode se chamar infinitamente, o que acabaria com os recursos do servidor. Para resolver isso, toda função recursiva deve ter uma condição de parada para que ela possa retornar valores até o local da primeira chamada e continuar seu processamento.
Em nível de banco de dados, o maior exemplo do uso de recursividade são as tabelas que possuem um auto-relacionamento (e normalmente uma relação de hierarquia entre os próprios registros dela). Para exemplificar o problema utilizaremos o modelo abaixo.
O modelo apresenta uma única tabela, chamada pessoas. O campo Id_Pessoa representa a chave primária da tabela que é o código da pessoa. O campo Id_Gerente é o gerente da pessoa. É uma chave estrangeira para a mesma tabela, ou seja, o valor de Id_Gerente deve ser um valor que já existe previamente na tabela no campo Id_Pessoa de outro registro. Um gerente pode gerenciar várias pessoas. O campo nível é um flag que será explicado mais adiante.
Os dados que serão utilizados são os seguintes:
Id_Pessoa |Nome |Cargo |Nível |Id_Gerente
1 |Carlos| Presidente| 1| NULL
2| Nelson| Diretor Financeiro| 2| 1
3| João| Diretor Administrativo| 2| 1
4| Daniel| Diretor de TI| 2| 1|
5| Elen| Gerente de RH| 3| 3
6| Mauro| Gerente de Infra| 3| 4|
7| Marilene| Gerente de Contabilidade| 3| 2
8| Luiz| Gerente de Desenvolvimento| 3| 4|
9| Luiza| Gerente de Arrecadação| 3| 2|
No exemplo, Carlos é o presidente da empresa. Nelson é gerenciado por Carlos; e Luiza e Marilene são gerenciadas por Nelson.
O problema em questão não é listar tabela inteira como mostrado anteriormente. O problema é exibir isso utilizando a ordenação de hierarquia, colocando cada pessoa abaixo do seu respectivo gerente.
Para iniciar a resolução deste problema, será utilizado uma junção da tabela com ela mesma (self join – para mais informações sobre self join, sugiro a leitura da matéria “SQL – Trabalhando com pares de linhas” do Mauro Pichiliani na SQL Magazine 27). O resultado pode ser visualizado na figura abaixo.
Com esta metodologia, apenas obtemos uma ligação da pessoa e seu respectivo gerente, mas não o gerente do gerente. Para resolver este problema, utilizaremos o campo nível. O campo nível é utilizado como um flag na tabela, contendo os valores 1, 2 e 3, que são os níveis hierárquicos. Desta forma, definiremos a tabela de pessoas três vezes na query: a primeira para o nível 1, a segunda para o 2 e a terceira para o 3, como mostrado na figura.
Desta forma, foi obtido uma ordenação. Mostrada na primeira coluna o mais alto grau, na segunda as gerências intermediárias e na terceira as gerências menores. Com um gerador de relatórios, poderiam ser realizadas quebras mostrando que uma gerência estaria dentro da outra, mas apenas pela ordem nas colunas podemos visualizar isso. O grande problema é: o que fazer se for adicionado um quarto e um quinto nível hierárquico? A cada nível a query se tornará mais complexa. Além disso, a query não veio hierarquizada diretamente da query, ou seja, precisamos de uma ferramenta externa pra “hierarquizar”.
Para resolver este problema, será utilizado uma stored procedure (poderia ser uma function) que utilizará recursão, ou seja, a procedure chamará ela mesma várias vezes. O campo nível não será mais necessário com a procedure, então executaremos o seguinte comando na tabela, para excluir a coluna nível da tabela.
ALTER TABLE PESSOAS DROP COLUMN Nivel
A idéia da procedure é incluir os registros na ordem desejada (hierárquica) em uma tabela temporária (para mais informações sobre tabelas temporárias acesse o link http://www.imasters....as_temporarias). Esta tabela tem apenas um campo para o nome da pessoa. Na procedure a tabela será chamada de #Temp.
Utilizaremos para esta procedure dois parâmetros. O primeiro é o nó pai, que, em geral, é o gerente máximo da organização. Essa pessoa normalmente não tem gerente, então o padrão é nulo. O segundo parâmetro é o nível, que por padrão é zero e será explicado mais adiante. O cabeçalho da procedure é descrito abaixo:
ALTER PROCEDURE SPR_ListaPessoasPorHierarquia (@Id_Gerente INT = NULL, @Nivel SMALLINT = 0) AS
Para esta procedure, será necessária uma navegação registro a registro, contudo, não é possível utilizar cursor, pois como a procedure se chama diversas vezes, será detectada a existência prévia do cursor. Desta forma, será realizada uma simulação de um cursor utilizando selects normais. O controle deste cursor será realizado pela cláusula ORDER BY com o campo Id_Pessoa. Existem duas situações diferentes a abertura deste cursor, que é quando a pessoa não tiver gerente e quando tiver. Isto faz com que a cláusula WHERE mude um pouco e isto é controlado por um comando IF, conforme código abaixo:
DECLARE @Id_Pessoa INT, @Id_PessoaAnt INT, @Nome VARCHAR(40) --Abrindo o "cursor" IF @Id_Gerente IS NULL SELECT TOP 1 @Id_Pessoa = Id_Pessoa, @Nome = Nome FROM Pessoas WHERE Id_Gerente IS NULL ORDER BY Id_Pessoa ELSE SELECT TOP 1 @Id_Pessoa = Id_Pessoa, @Nome = Nome FROM Pessoas WHERE Id_Gerente = @Id_Gerente ORDER BY Id_Pessoa
Com as variáveis carregadas, o laço é iniciado e controlado pela variável @Id_Pessoa (Esta é a condição de parada da recursividade). O código do laço pode ser visualizado abaixo:
WHILE @Id_Pessoa IS NOT NULL BEGIN --Código interno ao laço END
O código interno do laço realiza a inserção da pessoa na tabela temporária e faz a chamada recursiva da procedure. A variável @Nivel é utilizada para realizar a identação (visualização em árvore). Ela é utilizada em conjunto com a função do SQL Server SPACE, que retorna o número de espaços em branco informados no parâmetro.
Após a inserção, uma nova chamada da procedure é realizada (recursividade). Os parâmetros para a próxima “instância” da procedure é a pessoa atual, ou seja, serão selecionados as pessoas que a pessoa atual gerencia, e assim sucessivamente. A variável nível é incrementada e passada na nova chamada da procedure. Após o retorno da procedure, a variável nível é retornada para o valor atual, entretanto isso só acontecerá depois que todos o filhos da pessoa atual (e os filhos delas...) forem processados. O código pode ser visualizado abaixo:
--Inserindo registro na tabela temporária INSERT INTO #Temp(Nome) VALUES(SPACE((@Nivel) * 5) + @Nome) --Incrementando nível antes de chamar a procedure SET @Nivel = @Nivel + 1 --Buscando todas as pessoas gerenciadas pela pessoa atual EXECUTE SPR_ListaPessoasPorHierarquia @Id_Pessoa, @Nivel --Voltando ao nível anterior SET @Nivel = @Nivel - 1
Para buscar o próximo registro, será realizado uma nova consulta no banco de dados que irá carregar as variáveis @Id_Pessoa e @Nome. Para isso, é utilizada uma cláusula WHERE onde trará apenas registros com o campo Id_Pessoa maior que a pessoa que está sendo processada agora. Isso dá uma idéia de cursor, sem ser cursor. Como a variável @Id_Pessoa só é atualizada se um registro for encontrado, é utilizada uma variável temporária (@Id_PessoaAnt) na cláusula WHERE e a variável @Id_Pessoa é setada para NULL. Assim, se nenhum registro for encontrado, a procedure retorna para a “instância” anterior até chegar na primeira chamada. O código pode ser visto abaixo:
--Definindo variável para a pessoa anterior SET @Id_PessoaAnt = @Id_Pessoa SET @Id_Pessoa = NULL --Próxima pessoa IF @Id_Gerente IS NULL SELECT TOP 1 @Id_Pessoa = Id_Pessoa, @Nome = Nome FROM Pessoas WHERE Id_Gerente IS NULL AND Id_Pessoa > @Id_PessoaAnt ORDER BY Id_Pessoa ELSE SELECT TOP 1 @Id_Pessoa = Id_Pessoa, @Nome = Nome FROM Pessoas WHERE Id_Gerente = @Id_Gerente AND Id_Pessoa > @Id_PessoaAnt ORDER BY Id_Pessoa
Para finalizar a chamada da procedure, pode ser realizada com o código abaixo. Este código pode ser colocado dentro de outra procedure, mas neste caso é um script SQL normal. O resultado pode ser visualizado na figura abaixo:
O código completo da procedure pode ser visualizado abaixo:
ALTER PROCEDURE SPR_ListaPessoasPorHierarquia (@Id_Gerente INT = NULL, @Nivel SMALLINT = 0) AS DECLARE @Id_Pessoa INT, @Id_PessoaAnt INT, @Nome VARCHAR(40) --Abrindo o "cursor" IF @Id_Gerente IS NULL SELECT TOP 1 @Id_Pessoa = Id_Pessoa, @Nome = Nome FROM Pessoas WHERE Id_Gerente IS NULL ORDER BY Id_Pessoa ELSE SELECT TOP 1 @Id_Pessoa = Id_Pessoa, @Nome = Nome FROM Pessoas WHERE Id_Gerente = @Id_Gerente ORDER BY Id_Pessoa --Condição de parada: não encontrar mais pessoas WHILE @Id_Pessoa IS NOT NULL BEGIN --Inserindo registro na tabela temporária INSERT INTO #Temp(Nome) VALUES(SPACE((@Nivel) * 5) + @Nome) --Incrementando nível antes de chamar a procedure SET @Nivel = @Nivel + 1 --Buscando todas as pessoas gerenciadas pela pessoa atual EXECUTE SPR_ListaPessoasPorHierarquia @Id_Pessoa, @Nivel --Voltando ao nível anterior SET @Nivel = @Nivel - 1 --Definindo variável para a pessoa anterior SET @Id_PessoaAnt = @Id_Pessoa SET @Id_Pessoa = NULL --Próxima pessoa IF @Id_Gerente IS NULL SELECT TOP 1 @Id_Pessoa = Id_Pessoa, @Nome = Nome FROM Pessoas WHERE Id_Gerente IS NULL AND Id_Pessoa > @Id_PessoaAnt ORDER BY Id_Pessoa ELSE SELECT TOP 1 @Id_Pessoa = Id_Pessoa, @Nome = Nome FROM Pessoas WHERE Id_Gerente = @Id_Gerente AND Id_Pessoa > @Id_PessoaAnt ORDER BY Id_Pessoa END
Pessoal, por hora é isto. Espero que vocês tenham gostado.
No próximo artigo apresentarei uma alternativa a este desenvolvimento, que simplifica e muito esta abordagem com um recurso novo do SQL Server 2005, as CTE’s (Common Table Expression).
Até a próxima!
Artigo Original de :
http://www.imasters....ros_-_parte_01/
Lawrence Carvalho é MCP e DBA SQL Server, desenvolvedor em VB, VB .NET, Delphi e Genexus
