Modelagem de Dados: Hierarquias - Parte 1, por Gustavo Maia Aguiar

Utilizar uma extensão do banco de dados

Dada as limitações da SQL e a impossibilidade do padrão ANSI em prever todas as construções necessárias que a SQL devesse possuir, os fabricantes de bancos de dados embutiram algumas extensões proprietárias para complementar a SQL implementada em seus produtos montando assim, seus próprios dialetos. São exemplos de extensões o PL/SQL (Oracle), o Transact SQL (SQL Server), o pgPL/SQL (PostgreSQL), etc. O Oracle trabalha bem questões de recursividade através do operador CONNECT BY. Já no SQL Server 2005, a utilização de CTEs (Common Table Expressions) pode ser utilizada para resolver problemas com a recursividade. Abaixo segue uma construção de uma CTE para calcular o total de lançamentos do centro de custo “TI”.

DECLARE
@NomeCentroCusto VARCHAR(80),
@IdCentroCusto INT

SET @NomeCentroCusto = 'TI';
SET @IdCentroCusto = (SELECT IdCentroCusto FROM CentrosCusto WHERE NomeCentroCusto = @NomeCentroCusto);

-- Definição da CTE
WITH RelacoesHierarquicas
AS
-- Retornar dados de todas as relações hierárquicas existentes
(SELECT
    IdCentroCusto, NomeCentroCusto, IdCentroCustoSup
FROM
    CentrosCusto
WHERE
    IdCentroCusto = @IdCentroCusto

UNION ALL

SELECT
    CC.IdCentroCusto, CC.NomeCentroCusto, CC.IdCentroCustoSup
FROM
CentrosCusto AS CC
    INNER JOIN RelacoesHierarquicas AS RH
        ON CC.IdCentroCustoSup = RH.IdCentroCusto)

-- Combinar a lista de relações hierárquicas com a tabela de lançamentos
SELECT
    SUM(ValorLancamento) AS Total
FROM
    RelacoesHierarquicas AS RH
    INNER JOIN Lancamentos AS Lan
        ON RH.IdCentroCusto = Lan.IdCentroCusto

As alternativas empregadas foram utilizadas para consultas do tipo “TOP-DOWN”. Com algumas alterações de código, as mesmas alternativas podem ser utilizadas para consultas do tipo “BOTTOM-UP”. O código abaixo demonstra como a partir de um centro de custo é possível obter seus centros de custo superiores até a presidência. Se essa solução for melhorada, é possível relacionar os centros de custo com seus respectivos lançamentos e responder a outras consultas. Ex: Qual é o percentual do total em lançamentos de cada centro de custo inferior em relação a TI ?

DECLARE
@NomeCentroCusto VARCHAR(80),
@IdCentroCusto INT

SET @NomeCentroCusto = 'Copa'
SET @IdCentroCusto = (SELECT IdCentroCusto FROM CentrosCusto WHERE NomeCentroCusto = @NomeCentroCusto)

WHILE EXISTS (SELECT IdCentroCustoSup FROM CentrosCusto WHERE IdCentroCusto = @IdCentroCusto)
BEGIN
    PRINT @NomeCentroCusto
    SET @IdCentroCusto = (SELECT IdCentroCustoSup FROM CentrosCusto WHERE IdCentroCusto = @IdCentroCusto)
    SET @NomeCentroCusto = (SELECT NomeCentroCusto FROM CentrosCusto WHERE IdCentroCusto = @IdCentroCusto)
END