[firebase-br] Trabalhando com Hierarquia no Firebird

[Ivan Guimarães Meirelles]

Como havia prometido, segue ai um passo-a-passo para quem quiser trabalhar
com hierarquia no Firebird.

O exemplo que vou montar aqui será baseado em um cadastro de funcionários e
seus supervisores, que também são funcionários:

1 - Crie a seguinte tabela:

CREATE TABLE FUNCIONARIOS
(
    IDFUNCIONARIO INTEGER NOT NULL,
    NOME VARCHAR(30),
    IDSUPERVISOR INTEGER
);

2 - Defina o campo IDFUNCIONARIO como chave primária:

alter table FUNCIONARIOS
     add constraint PK_FUNCIONARIOS primary key (IDFUNCIONARIO);
                
3 - Crie um auto-relacionamento nessa tabela, ligando IDSUPERVISOR a
IDFUNCIONARIO, criando uma chave estrangeira:

alter table FUNCIONARIOS
     add constraint FK_FUNC_SUPE foreign key (IDSUPERVISOR)
     references FUNCIONARIOS(IDFUNCIONARIO) on update CASCADE;
                
4 - Vamos alimentar essa tabela. Obs.: O funcionário que não tiver
supervisor, terá o campo IDSUPERVISOR como NULL:

insert into funcionarios(idfuncionario, nome, idsupervisor) values(1, 'MARIA
APARECIDA', null);
insert into funcionarios(idfuncionario, nome, idsupervisor) values(2, 'JOAO
DA SILVA', 1);
insert into funcionarios(idfuncionario, nome, idsupervisor) values(3,
'AGUINALDO OLIVEIRA', 1);
insert into funcionarios(idfuncionario, nome, idsupervisor) values(4,
'RENATA FRANCA', 3);
insert into funcionarios(idfuncionario, nome, idsupervisor) values(5,
'GISELE SOUZA', 3);
insert into funcionarios(idfuncionario, nome, idsupervisor) values(6, 'LAURA
BONIELI', 3);
insert into funcionarios(idfuncionario, nome, idsupervisor) values(7, 'HILDA
CONCEICAO', 6);
insert into funcionarios(idfuncionario, nome, idsupervisor) values(8,
'RICARDO SOUZA', 6);
insert into funcionarios(idfuncionario, nome, idsupervisor) values(9,
'MARCOS DUILIO', 8);

5 - Precisaremos aqui, declarar duas UDF's: STRLEN e LPAD, essas UDF's fazem
parte da instalação do Firebird:

DECLARE EXTERNAL FUNCTION STRLEN
    CSTRING(32767)
RETURNS INTEGER BY VALUE
ENTRY_POINT 'IB_UDF_strlen' MODULE_NAME 'ib_udf';

DECLARE EXTERNAL FUNCTION LPAD
    CSTRING(255),
    INTEGER,
    CSTRING(1)
RETURNS CSTRING(255) FREE_IT
ENTRY_POINT 'IB_UDF_lpad' MODULE_NAME 'ib_udf';

6 - Criaremos agora uma Stored Procedure Recursiva para similar uma
funcionalidade do Oracle, quando se trata de hierarquia:

CREATE PROCEDURE FUNC_HIERARQ (
    id integer,
    idinicial varchar(30))
returns (
    codigo varchar(30),
    nome varchar(30),
    idfuncionario integer,
    idsupervisor integer,
    nivel integer)
as
declare variable aux integer;
begin

    if (id is null) then
        begin
            for
                select lpad(FC.idfuncionario, 2, '0'), FC.nome,
FC.idfuncionario, FC.idsupervisor 
      from FUNCIONARIOS FC where FC.idsupervisor is null
            into
                :codigo, :nome, :idfuncionario, :idsupervisor
            do
                begin
                    nivel = (strlen(:codigo) + 1) / 3;
                    suspend;
                    for select FH.codigo, FH.nome, FH.idfuncionario,
FH.idsupervisor 
    from FUNC_HIERARQ(:codigo, :codigo) FH
                    into :codigo, :nome, :idfuncionario, :idsupervisor
                    do
                        begin
                            nivel = (strlen(:codigo) + 1) / 3;
                            suspend;
                        end
                end
        end

    if (not id is null) then
        begin
            for
                select coalesce(:idinicial,'') || '.' ||
lpad(FC.idfuncionario, 2, '0'), FC.nome, FC.idfuncionario, FC.idsupervisor
      from FUNCIONARIOS FC where FC.idsupervisor = :id
            into
                :codigo, :nome, :aux, :idsupervisor
            do
                begin
                    idfuncionario = :aux;
                    nivel = (strlen(:codigo) + 1) / 3;
                    suspend;
                    for select FH.codigo, FH.nome, FH.idfuncionario,
FH.idsupervisor from FUNC_HIERARQ(:aux, :codigo) FH
                    into :codigo, :nome, :idfuncionario, :idsupervisor
                    do
                        begin
                            nivel = (strlen(:codigo) + 1) / 3;
                            suspend;
                        end
                end
        end

end;

7 - A Stored Procedure abaixo mostra um exemplo de utilização da procedure
FUNC_HIERARQ:

CREATE PROCEDURE LISTA_FUNCIONARIOS returns ( relacao varchar(100) )
as
begin
    for SELECT LPAD(nome, STRLEN(nome) + (nivel * 10) - 10, ' ') FROM
func_hierarq(null, null)
        into :relacao do suspend;
end;

Depois de ter criado as procedures, para usá-las basta fazer assim:

select * from func_hierarq(null, null) --<-- e vc terá o seguinte retorno:

CODIGO         | NOME               | IDFUNCIONARIO | IDSUPERVISOR | NIVEL
---------------+--------------------+---------------+--------------+-------
01             | MARIA APARECIDA    | 1             | <NULL>       | 1
01.02          | JOAO DA SILVA      | 2             | 1            | 2
01.03          | AGUINALDO OLIVEIRA | 3             | 1            | 2
01.03.04       | RENATA FRANCA      | 4             | 3            | 3
01.03.05       | GISELE SOUZA       | 5             | 3            | 3
01.03.06       | LAURA BONIELI      | 6             | 3            | 3
01.03.06.07    | HILDA CONCEICAO    | 7             | 6            | 4
01.03.06.08    | RICARDO SOUZA      | 8             | 6            | 4
01.03.06.08.09 | MARCOS DUILIO      | 9             | 8            | 5

ou então:

select * from lista_funcionarios --<-- e vc terá o seguinte retorno:

RELACAO
----------------------------------
MARIA APARECIDA
     JOAO DA SILVA
     AGUINALDO OLIVEIRA
          RENATA FRANCA
          GISELE SOUZA
          LAURA BONIELI
               HILDA CONCEICAO
               RICARDO SOUZA
                    MARCOS DUILIO


Agora é com vocês... isso foi apenas uma idéia do que é possível se fazer
com o FireBird... basta ter um pouco de criatividade.
Por exemplo: Grupos e subgrupos de mercadorias com hierarquia, Centros de
Custo, Planos de Contas, Balancetes e muito mais...

Pedras, Xingamentos, Elogios, Dúvidas e/ou Sugestões... podem me contatar
por e-mail.

Um abraço a todos...

Ivan Guimarães Meirelles
Três Lagoas/MS - igmeirelles em gmail.com