Trabalhando com Queries Hierarquicas. Relação de Registro Pai com Registro Filho na mesma tabela.

Quando precisamos trabalhar com essa relação de registro pai e filho na mesma tabela, ficamos logo pensando como fazer isso, sem gerar problemas no desempenho das queries.
Pensando nisso, a Oracle tem uma função chamada Connect By. Onde podemos criar queries com hierarquia, podendo empregar este recurso na contabilidade, setores, empregados, mas todos com sentido de árvore (Tree View).

Sintaxe:
START WITH CONNECT BY [NOCYCLE] ou
START WITH [NOCYCLE] CONNECT BY
Vamos colocar a mão na massa, iremos criar uma tabela onde terá as informações de cargos de um determinado setor de informática.
Segue a estrutura da Tabela:
CREATE TABLE CARGO(
COD_CARGO NUMBER NOT NULL,
COD_CARGO_PAI NUMBER,
DESCRICAO VARCHAR(20),
INCLUSAO DATE NOT NULL);
Agora criando os constraints da tabela:
ALTER TABLE CARGO ADD CONSTRAINT PK_CARGO PRIMARY KEY (COD_CARGO) USING INDEX;
ALTER TABLE CARGO ADD CONSTRAINT FK_CARGO FOREIGN KEY (COD_CARGO) REFERENCES CARGO (COD_CARGO) ON DELETE CASCADE;
Para obter uma consulta hierárquica, seguiremos os seguintes parâmetros:
SELECT COD_CARGO, COD_CARGO_PAI, LEVEL, DESCRICAO, INCLUSAO
FROM CARGO
START WITH COD_CARGO_PAI ISNULL
CONNECT BY PRIOR COD_CARGO = COD_CARGO_PAI
ORDER BY LEVEL
O retorno da consulta:
COD_CARGO  COD_CARGO_PAI  LEVEL  DESCRICAO                    INCLUSAO
2                                                             1              DIRETOR                       01/08/2012 13:29:10
3                         2                                  2              SUPERINTENDENTE 01/08/2012 13:32:10
1                         2                                  2              GERENTE                      01/08/2012 13:32:10
5                         1                                  3              ANALISTA                    01/08/2012 13:32:10
A Coluna Level, vai indicar o nível hierárquico, e se retornar 1 é o pai, 2 é o filho e 3 o neto.
A Clausula Start With cod_cargo_pai is null, vai indica o início da hierarquia.
O connect by prior, vai realizar a condição de ligação entre o pai e o filho.
Ref. http://psoug.org/reference/connectby.html
Espero que gostem e até a próxima!
 

CooperaTI

Administrador e coordenador do site!

k2cloud.com.br