[firebase-br] Trabalhando com Hierarquia no Firebird
satoy.matsuoka em hstern.com.br
satoy.matsuoka em hstern.com.br
Qua Ago 29 09:20:50 -03 2007
Bom dia Ivan,
Perfeito !!!
Parabéns !!!!
Obrigado
[]'s
Satoy
Ivan Guimarães Meirelles <igmeirelles em gmail.com>
27/08/2007 07:21
To
"'FireBase'" <lista em firebase.com.br>, <satoy.matsuoka em hstern.com.br>
cc
Subject
Trabalhando com Hierarquia no Firebird
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
Mais detalhes sobre a lista de discussão lista