artigo SQL Magazine 11 - Evolução das consultas hierárquicas do Oracle 9i ao 10g.

Você precisa estar logado para dar um feedback. Clique aqui para efetuar o login
Para efetuar o download você precisa estar logado. Clique aqui para efetuar o login
Confirmar voto
0
 (1)  (0)

Artigo da Revista SQL Magazine -Edição 11.

Clique aqui para ler esse artigo em PDF.imagem_pdf.jpg

capnet43.jpg

Clique aqui para ler todos os artigos desta edição

Evolução das consultas hierárquicas do Oracle 9i ao 10g.

 

Alguns aplicativos utilizam dados hierárquicos. Como exemplo temos aqueles que necessitam manipular organogramas, lista de materiais de uma fábrica de montagem ou árvores genealógicas. Esses tipos de informação são representados de forma mais conveniente em uma estrutura de árvore e, apesar do modelo relacional não ter sido elaborado para esse tipo de informação, eles podem ser facilmente organizados em uma tabela relacional por meio do uso de uma relação de auto-referência.

O Oracle fornece algumas extensões úteis ao ANSI SQL para manipulação dos dados hierárquicos representados em uma tabela relacional. Com as extensões fornecidas, você pode construir operações hierárquicas complexas em dados estruturados em árvore. Este artigo faz uma introdução aos dados hierárquicos abordando a criação de consultas no Oracle 9i e 10g.

Como os dados são cadastrados?

Para analisarmos o funcionamento de relações de auto-referência, será utilizado inicialmente como exemplo uma tabela para cadastrar localidades de um país (estados, cidades e bairros). Perceba que um país é composto de vários estados e estes estados são compostos de várias cidades e estas cidades possuem muitos bairros, ou seja, um exemplo claro de hierarquia. Veja o código para criação da tabela na Listagem 1.

 

CREATE TABLE LOCALIDADES

(

          COD_LOCAL                     NUMBER(5),

          COD_LOCAL_PAI               NUMBER(5),

          NOM_LOCAL                     VARCHAR2(30),

          PRIMARY KEY (COD_LOCAL),

          FOREIGN KEY (COD_LOCAL_PAI) REFERENCES LOCALIDADES (COD_LOCAL)

);

Listagem 1.

 

No código acima, o campo Cod_Local_Pai é chave estrangeira do campo Cod_Local da mesma tabela. O auto-relacionamento está definido. Em uma tabela de auto-relacionamento, no primeiro nível de uma hierarquia, o campo que se relaciona com a chave primária fica com o valor vazio. No nosso exemplo, esse campo é COD_LOCAL_PAI. Veja na Figura 1 um exemplo de como ficaria a organização dos locais, sendo o Brasil o 1º nível da hierarquia. Perceba também que a hierarquia possui cinco níveis. A Listagem 2 apresenta o código necessário para inserção destes dados na tabela Localidades.

 

Brasil

Rio de Janeiro

Paraná

São Paulo

Rio de Janeiro

Jacarepaguá

Madureira

Curitiba

Batel

Água Verde

Taquara

Freguesia

Figura 1. 

 

INSERT INTO LOCALIDADES (COD_LOCAL, NOM_LOCAL) VALUES (1, ‘Brasil’);

 

INSERT INTO LOCALIDADES (COD_LOCAL, NOM_LOCAL, COD_LOCAL_PAI)

VALUES (2, ‘Rio de Janeiro’, 1);

 

INSERT INTO LOCALIDADES (COD_LOCAL, NOM_LOCAL, COD_LOCAL_PAI)

VALUES (3, ‘Rio de Janeiro’, 2);

 

INSERT INTO LOCALIDADES (COD_LOCAL, NOM_LOCAL, COD_LOCAL_PAI)

VALUES (4, ‘Jacarepaguá’, 3);

 

INSERT INTO LOCALIDADES (COD_LOCAL, NOM_LOCAL, COD_LOCAL_PAI)

VALUES (5, ‘Madureira’, 3);

 

INSERT INTO LOCALIDADES (COD_LOCAL, NOM_LOCAL, COD_LOCAL_PAI)

VALUES (6, ‘Taquara’, 4);

 

INSERT INTO LOCALIDADES (COD_LOCAL, NOM_LOCAL, COD_LOCAL_PAI)

VALUES (7, ‘Freguesia’, 4);

 

INSERT INTO LOCALIDADES (COD_LOCAL, NOM_LOCAL, COD_LOCAL_PAI)

VALUES (8, 'São Paulo', 1);

 

INSERT INTO LOCALIDADES (COD_LOCAL, NOM_LOCAL, COD_LOCAL_PAI)

VALUES (9, 'Paraná', 1);

 

INSERT INTO LOCALIDADES (COD_LOCAL, NOM_LOCAL, COD_LOCAL_PAI)

VALUES (10, 'Curitiba', 9);

 

INSERT INTO LOCALIDADES (COD_LOCAL, NOM_LOCAL, COD_LOCAL_PAI)

VALUES (11, 'Água Verde', 10);

 

INSERT INTO LOCALIDADES (COD_LOCAL, NOM_LOCAL, COD_LOCAL_PAI)

VALUES (12, 'Batel', 10);

Listagem 2.

 

Percebam que ao cadastrar o Brasil o código pai não foi informado pois ele está no primeiro nível da hierarquia.

Consultando os Dados

O Oracle 9i possui duas cláusulas que permitem uma leitura completa dos dados de uma tabela com auto-relacionamento:

 

·         Connect By: responsável por informar ao Oracle como o auto-relacionamento ocorre. É nela que é informada qual coluna deve ser comparada à coluna pai correspondente ao relacionamento. Nesta cláusula, podemos utilizar a palavra reservada prior que é responsável por manter uma referência para o nível ancestral da hierarquia.

·         Start With: informa ao Oracle em qual nível inicial de hierarquia os registros serão pesquisados. Sem esse comando, o Oracle não sabe quantos ramos existem na hierarquia implicando na consideração de cada registro como o nível inicial da hierarquia, tentando desta forma, gerar uma árvore hierárquica para cada registro.

 

A Listagem 3 apresenta o código para recuperar os dados da tabela Localidades de acordo com a hierarquia presente no auto-relacionamento. O resultado da consulta pode ser visualizada na Figura 2.

 

SELECT COD_LOCAL, NOM_LOCAL, COD_LOCAL_PAI

FROM LOCALIDADES

CONNECT BY PRIOR COD_LOCAL = COD_LOCAL_PAI

START WITH COD_LOCAL_PAI IS NULL;

Listagem 3.

 

  

Figura 2.       

 

No exemplo, informamos na cláusula Start With que a pesquisa deverá ser iniciada pelos registros onde o código pai da localidade é nulo (no nosso caso o único existente é o Brasil). Na cláusula Connect By, informamos que o auto-relacionamento se dá com o COD_LOCAL igual a COD_LOCAL_PAI. Neste caso, a palavra reservada prior indica que o campo COD_LOCAL deverá ser procurado no nível ancestral da hierarquia e comparado com o valor de COD_LOCAL_PAI do registro atual.

Assim, o percurso seguido pela pesquisa começa no nível definido no comando Start With e é percorrido linha a linha, onde em cada nível os nós são lidos da esquerda para a direita. Deste modo, os passos seguidos na execução da consulta da Listagem 3 são:

 

1.      É verificada no 2º nível da hierarquia a existência de pelo menos um nó;

2.      Existindo nós, a pesquisa começa pelo nó mais à esquerda;

3.      Os dados do nó são retornados e se o mesmo tiver ramificações, os dados dos nós posteriores são retornados obedecendo à ordem de pesquisa que é da esquerda para a direita;

4.      Ao terminar a pesquisa do nó mais à esquerda, serão retornados os dados do segundo nó do 2º nível;

5.      A pesquisa termina ao chegar no último nó da árvore hierárquica.

 

Nota

É importante o leitor atentar para o uso da palavra PRIOR. Seu uso não é obrigatório, mas o efeito de não usá-lo é bastante diferente. Isto por que a cláusula PRIOR garante a ligação com a linha anterior da hierarquia. Sem o seu uso, apenas o primeiro elemento da hierarquia é recuperado. Por exemplo, para a consulta apresentada na listagem 3 sem o PRIOR, teríamos como resultado apenas o nó Brasil.

 

A Pseudocoluna Level

Ao utilizar a cláusula Connect By em um comando SQL, é possível verificar o nível hierárquico do registro listado. Para isto temos a coluna Level, exemplificada na Listagem 4 e na Figura 3.

 

SELECT LEVEL, COD_LOCAL,  NOM_LOCAL

FROM LOCALIDADES

CONNECT BY PRIOR COD_LOCAL = COD_LOCAL_PAI

START WITH COD_LOCAL_PAI IS NULL;

Listagem 4.

 


Figura 3.

      

O uso desta coluna é importante, por exemplo, quando quisermos filtrar o resultado da pesquisa. A Listagem 5 apresenta uma consulta onde são retornados apenas os registros do terceiro nível da hierarquia, ou seja, as cidades. O resultado da consulta pode ser visto na Figura 4.

 

SELECT  COD_LOCAL,  NOM_LOCAL

FROM LOCALIDADES

WHERE LEVEL = 3

CONNECT BY PRIOR COD_LOCAL = COD_LOCAL_PAI

START WITH COD_LOCAL_PAI IS NULL;

Listagem 5.

 

Figura 4.


Endentando o Resultado da Pesquisa

Para uma melhor visualização da árvore hierárquica, podemos utilizar a função RPAD para endentar os registros de acordo com seu nível. O RPAD é uma função que possui 2 parâmetros, o texto que será retornado pela função e o espaçamento da endentação. A Listagem 6 apresenta um exemplo de uso da endentação e a Figura 5, seu resultado.

 

SELECT  RPAD(‘ ’, LEVEL*5 – 5) || NOM_LOCAL AS LOCAL

FROM LOCALIDADES

CONNECT BY PRIOR COD_LOCAL = COD_LOCAL_PAI

START WITH COD_LOCAL_PAI IS NULL;

Listagem 6.

 

 


Figura 5.

Diferenças entre o Oracle 8i e Oracle 9i

·         No Oracle 8i, a cláusula Connect By não pode ser utilizada em uma consulta que possua Joins. No Oracle 9i, esta restrição foi removida. A Listagem 7 apresenta um exemplo no qual serão retornados os clientes que moram na cidade do Rio de Janeiro.

 

SELECT C.NOME, L. NOM_LOCAL

FROM LOCALIDADES L, CLIENTES C

WHERE C.COD_CIDADE = L.COD_LOCAL

CONNECT BY PRIOR COD_LOCAL = COD_LOCAL_PAI

START WITH LEVEL = 3 AND UPPER(NOM_LOCAL) = ‘RIO DE JANEIRO’;

Listagem 7.

 

·         No Oracle 8i, a clausula Connect By só pode ser utilizada com tabelas. Já no Oracle 9i, ela pode ser utilizada com Visões (Views), tabelas externas e outros objetos.

·         No Oracle 9i foi criada uma função chamada SYS_CONNECT_BY_PATH que pode ser usada para descrever o caminho hierárquico de um registro. A função possui dois parâmetros. O primeiro é a coluna que conterá a informação para a qual o caminho será especificado e o segundo é o caractere que será utilizado como separador dos níveis existentes no caminho. A Listagem 8 apresenta a consulta utilizada para listar as localidades e o caminho percorrido para alcançá-la. Seu resultado pode ser visto na Figura 7.

 

SELECT NOM_LOCAL, SYS_CONNECT_BY_PATH(NOM_LOCAL, ‘\’) AS CAMINHO

FROM LOCALIDADES

CONNECT BY PRIOR COD_LOCAL = COD_LOCAL_PAI

START WITH COD_LOCAL_PAI IS NULL;

Listagem 8.

 

Figura 7.

 

Vimos até aqui uma introdução às consultas hierárquicas e como o tratamento fornecido a elas evoluiu do Oracle 8i para o 9i. A partir de agora discutiremos novos avanços acrescentados na versão 10g do Oracle.

Melhorias na Consulta Hierárquica do Oracle 10g

A partir de agora utilizaremos a tabela EMPLOYEE para apresentar as novidades que o Oracle 10g traz no tratamento às consultas hierárquicas. A Listagem 9 apresenta o comando para criação da tabela. 

 

CREATE TABLE EMPLOYEE (

EMP_ID                  NUMBER                  (4)     CONSTRAINT EMP_PK PRIMARY KEY,

EMP_NAME    VARCHAR2     (15)    NOT NULL,

DEPT_ID                 NUMBER                  (2)     NOT NULL,

MGR_ID                  NUMBER                  (4)     CONSTRAINT EMP_FK REFERENCES EMPLOYEE(EMP_ID),

SALARY                  NUMBER                  (7,2)   NOT NULL,

HIRE_DATE    DATE            NOT NULL);

Listagem 9.

 

Observe novamente a limitação da chave externa (foreign key constraint) EMP_FK na coluna MGR_ID, que faz referência à coluna EMP_ID da mesma tabela. Nesta tabela, MGR_ID representa a EMP_ID do gerente de um funcionário.

Como vimos, até a versão 9i, as extensões hierárquicas do Oracle incluíam a cláusula START WITH … CONNECT BY e a pseudocoluna LEVEL. Ainda utilizando os recursos presentes no Oracle 9i, o exemplo da Listagem 10 mostra os funcionários por ordem hierárquica e utiliza um recuo de linha para posicionar os subordinados abaixo deles.

 

SELECT LEVEL, LPAD(' ',2*(LEVEL - 1)) || EMP_NAME "EMPLOYEE", EMP_ID, MGR_ID

FROM EMPLOYEE

START WITH MGR_ID IS NULL

CONNECT BY PRIOR EMP_ID = MGR_ID;

 

     LEVEL EMPLOYEE                 EMP_ID     MGR_ID

---------- -------------------- ---------- ----------

         1 KING                       7839

         2   JONES                    7566       7839

         3     SCOTT                  7788       7566

         4       ADAMS                7876       7788

         3     FORD                   7902       7566

         4       SMITH                7369       7902

         2   BLAKE                    7698       7839

         3     ALLEN                  7499       7698

         3     WARD                   7521       7698

         3     MARTIN                 7654       7698

         3     TURNER                 7844       7698

         3     JAMES                  7900       7698

         2   CLARK                    7782       7839

         3     MILLER                 7934       7782

Listagem 10.

 

Os novos recursos de consulta hierárquica do Oracle 10g são:

 

·         Novo operador

o        CONNECT_BY_ROOT

·         Novas pseudocolunas

o        CONNECT_BY_ISCYCLE

o        CONNECT_BY_ISLEAF

·         Novas palavras-chave

o        NOCYCLE

o        SIBLINGS

CONNECT_BY_ROOT

O operador CONNECT_BY_ROOT, quando aplicado a uma coluna, retorna o valor daquela coluna para a linha raiz, ou seja, agrupa o resultado pelo nó pai. O exemplo da Listagem 11 mostra como o operador CONNECT_BY_ROOT é usado.

 

SELECT EMP_NAME “Employee”, CONNECT_BY_ROOT EMP_NAME “Top Manager”

FROM EMPLOYEE

START WITH MGR_ID = 7839

CONNECT BY PRIOR EMP_ID = MGR_ID;

 

SELECT EMP_NAME “Employee”, CONNECT_BY_ROOT EMP_NAME “Top Manager”
FROM EMPLOYEE
START WITH MGR_ID = 7839
CONNECT BY PRIOR EMP_ID = MGR_ID;

 

Employee             Top Manager

-------------------- ------------

JONES                JONES

SCOTT                JONES

ADAMS                JONES

FORD                 JONES

SMITH                JONES

BLAKE                BLAKE

ALLEN                BLAKE

WARD                 BLAKE

MARTIN               BLAKE

TURNER               BLAKE

JAMES                BLAKE

CLARK                CLARK

MILLER               CLARK

Listagem 11.

 

Neste exemplo, o organograma é criado a partir das linhas que têm MGR_ID = 7839. Ou seja, qualquer funcionário cujo gerente seja “7839” será considerado uma raiz para essa consulta. Com isto, os funcionários incluídos abaixo do organograma sob essas raízes serão exibidos no conjunto de resultados juntamente com o nome de seu gerente. Assim, o operador CONNECT_BY_ROOT determina o primeiro nó superior na árvore para uma determinada linha agrupando os resultados por ele.

NOCYCLE

Não são permitidos ciclos em uma estrutura de árvore, no entanto, alguns dados hierárquicos podem contê-los. O problema é que às vezes é difícil identificá-los e a construção hierárquica “START WITH … CONNECT BY … PRIOR” reportará um erro se houver um ciclo nos dados.

Para permitir que “START WITH … CONNECT BY … PRIOR” funcione apropriadamente mesmo com a presença de ciclos, o Oracle 10g oferece uma nova palavra-chave, NOCYCLE. Se houver ciclos nos dados, você poderá utilizá-la na cláusula CONNECT BY e não receberá o erro mencionado anteriormente.

Os dados de teste que utilizamos na tabela EMPLOYEE não têm nenhum ciclo. Para testar o recurso NOCYCLE, vamos introduzir um ciclo nos dados da tabela EMPLOYEE. Para fazer isso, atualizamos a coluna MGR_ID do primeiro funcionário na hierarquia (KING com EMP_ID=7839) com a EMP_ID de um funcionário de nível mais baixo (MARTIN com EMP_ID = 7654) (ver Listagem 12).

 

UPDATE EMPLOYEE
SET MGR_ID = 7654
WHERE MGR_ID IS NULL;

Listagem 12.

 

Vejamos agora o que acontece se executarmos uma consulta hierárquica. Perceba que você receberá um erro ORA-01436 (ver Listagem 13).

 

SELECT LEVEL, LPAD(' ',2*(LEVEL - 1)) || EMP_NAME "EMPLOYEE", EMP_ID, MGR_ID
FROM EMPLOYEE
START WITH EMP_ID = 7839
CONNECT BY PRIOR EMP_ID = MGR_ID;

 

     LEVEL EMPLOYEE                 EMP_ID     MGR_ID

---------- -------------------- ---------- ----------

         1 KING                       7839       7654

         2   JONES                    7566       7839

         3     SCOTT                  7788       7566

         4       ADAMS                7876       7788

         3     FORD                   7902       7566

         4       SMITH                7369       7902

         2 BLAKE                      7698       7839

         3     ALLEN                  7499       7698

         3     WARD                   7521       7698

         3     MARTIN                 7654       7698

         4       KING                 7839       7654

         5         JONES              7566       7839

         6           SCOTT            7788       7566

         7             ADAMS          7876       7788

         6           FORD             7902       7566

ERROR:

ORA-01436: CONNECT BY loop in user data

Listagem 13.

 

Além do erro, observe que toda a estrutura da árvore (que inicia com KING) começa a se repetir abaixo de MARTIN tornando a visualização confusa. A palavra-chave NOCYCLE pode ser usada na cláusula CONNECT BY para eliminar esse erro (ver Listagem 14).

 

SELECT LEVEL, LPAD('  ',2*(LEVEL - 1)) || EMP_NAME "EMPLOYEE", EMP_ID, MGR_ID
FROM EMPLOYEE
START WITH EMP_ID = 7839
CONNECT BY NOCYCLE PRIOR EMP_ID = MGR_ID;

 

     LEVEL             EMPLOYEE     EMP_ID     MGR_ID

---------- -------------------- ---------- ----------

         1             KING           7839       7654

         2               JONES        7566       7839

         3             SCOTT          7788       7566

         4               ADAMS        7876       7788

         3             FORD           7902       7566

         4               SMITH        7369       7902

         2             BLAKE          7698       7839

         3               ALLEN        7499       7698

         3               WARD         7521       7698

         3               MARTIN       7654       7698

         3               TURNER       7844       7698

         3               JAMES        7900       7698

         2             CLARK          7782       7839

         3               MILLER       7934       7782

Listagem 14.

 

A consulta acima reconhece que existe um ciclo, ignora-o (como resultado da palavra-chave NOCYCLE) e retorna as linhas como se não houvesse ciclos.

CONNECT_BY_ISCYCLE

Às vezes, é difícil identificar ciclos em dados hierárquicos. A nova pseudocoluna do Oracle 10g, CONNECT_BY_ISCYCLE, poderá ajudá-lo a identificar facilmente os ciclos nos dados. CONNECT_BY_ISCYCLE só poderá ser usada juntamente com a palavra-chave NOCYCLE em uma consulta hierárquica. A pseudocoluna CONNECT_BY_ISCYCLE retorna 1 se a linha atual possui uma linha-filha que seja também uma linha-ancestral; caso contrário, ela retornará 0. Veja a Listagem 15.

 

SELECT EMP_NAME, CONNECT_BY_ISCYCLE
FROM EMPLOYEE
START WITH EMP_ID = 7839
CONNECT BY NOCYCLE PRIOR EMP_ID = MGR_ID;

 

EMP_NAME        CONNECT_BY_ISCYCLE

--------------- ------------------

KING                             0

JONES                            0

SCOTT                            0

ADAMS                            0

FORD                             0

SMITH                            0

BLAKE                            0

ALLEN                            0

WARD                             0

MARTIN                           1

TURNER                           0

JAMES                            0

CLARK                            0

MILLER                           0

Listagem 15.

 

Observe que, como MARTIN é gerente de KING neste conjunto de dados e está também abaixo de KING na árvore, a linha de MARTIN mostra o valor 1 para CONNECT_BY_ISCYCLE.

 

Nota

Para obter resultados corretos nas pesquisas subseqüentes, devemos retornar os dados ao estado original, atualizando a MGR_ID de KING para NULL.

CONNECT_BY_ISLEAF

Em uma estrutura de árvore, os nós que não possuem filhos são chamados de nós folha. CONNECT_BY_ISLEAF é uma pseudocoluna que retorna 1 se a linha atual é uma folha ou 0 caso contrário. Veja exemplo da Listagem 16.

 

SELECT EMP_NAME, CONNECT_BY_ISLEAF

FROM EMPLOYEE

START WITH EMP_ID = 7839

CONNECT BY PRIOR EMP_ID = MGR_ID;

 

EMP_NAME         CONNECT_BY_ISLEAF

---------------  -----------------

KING                             0

JONES                            0

SCOTT                            0

ADAMS                            1

FORD                             0

SMITH                            1

BLAKE                            0

ALLEN                            1

WARD                             1

MARTIN                           1

TURNER                           1

JAMES                            1

CLARK                            0

MILLER                           1

Listagem 16.

 

Este novo recurso pode ajudar a simplificar as instruções SQL que precisam identificar todos os nós folha. Sem a pseudocoluna, para identificá-los, você provavelmente escreveria uma consulta como a apresentada na Listagem 17.

 

SELECT EMP_ID, EMP_NAME, SALARY, HIRE_DATE
FROM EMPLOYEE E
WHERE NOT EXISTS
(SELECT EMP_ID FROM EMPLOYEE E1 WHERE E.EMP_ID = E1.MGR_ID);

 

EMP_ID  EMP_NAME        SALARY    HIRE_DATE

------- --------------- ---------- ---------

7369     SMITH                 800 17-DEC-80

7499     ALLEN                1600 20-FEB-81

7521     WARD                 1250 22-FEB-81

7654     MARTIN               1250 28-SEP-81

7844     TURNER               1500 08-SEP-81

7876     ADAMS                1100 23-MAY-87

7900     JAMES                 950 03-DEC-81

7934     MILLER               1300 23-JAN-82

Listagem 17.

 

É possível simplificar ainda mais essa consulta com a nova pseudocoluna CONNECT_BY_ISLEAF, conforme mostrado na Listagem 18.

 

SELECT EMP_ID, EMP_NAME, SALARY, HIRE_DATE
FROM EMPLOYEE E
WHERE CONNECT_BY_ISLEAF = 1
START WITH MGR_ID IS NULL
CONNECT BY PRIOR EMP_ID = MGR_ID;

 

EMP_ID EMP_NAME            SALARY HIRE_DATE

------- --------------- ---------- ---------

7876    ADAMS                 1100 23-MAY-87

7369    SMITH                  800 17-DEC-80

7499    ALLEN                 1600 20-FEB-81

7521    WARD                  1250 22-FEB-81

7654    MARTIN                1250 28-SEP-81

7844    TURNER                1500 08-SEP-81

7900    JAMES                  950 03-DEC-81

7934    MILLER                1300 23-JAN-82

Listagem 18.

ORDER SIBLINGS BY

A palavra-chave SIBLINGS foi introduzida no Oracle9i. No entanto, faz sentido discutí-la junto com os outros aprimoramentos do Oracle 10g. Uma consulta hierárquica com uma construção “START WITH … CONNECT BY … PRIOR … “ exibe os resultados em uma ordem arbitrária, conforme mostra o exemplo da Listagem 19.

 

SELECT LEVEL, LPAD('  ',2*(LEVEL - 1)) || EMP_NAME "EMPLOYEE", EMP_ID, MGR_ID
FROM EMPLOYEE
START WITH EMP_ID = 7839
CONNECT BY PRIOR EMP_ID = MGR_ID;

 

  LEVEL EMPLOYEE                 EMP_ID     MGR_ID

------- -------------------- ---------- ----------

1 KING                             7839

2   JONES                          7566       7839

3     SCOTT                        7788       7566

4       ADAMS                      7876       7788

3     FORD                         7902       7566

4       SMITH                      7369       7902

2   BLAKE                          7698       7839

3     ALLEN                        7499       7698

3     WARD                         7521       7698

3     MARTIN                       7654       7698

3     TURNER                       7844       7698

3     JAMES                        7900       7698

2 CLARK                            7782       7839

3   MILLER                         7934       7782

Listagem 19.

 

Como sempre, você pode usar uma cláusula ORDER BY para ordenar as linhas de resultados da maneira que desejar. No entanto, neste caso, o uso de uma cláusula ORDER BY pode destruir as camadas hierárquicas dos dados exibidos, conforme mostra o exemplo da Listagem 20.

 

SELECT LEVEL, LPAD('  ',2*(LEVEL - 1)) || EMP_NAME "EMPLOYEE", EMP_ID, MGR_ID
FROM EMPLOYEE
START WITH EMP_ID = 7839
CONNECT BY PRIOR EMP_ID = MGR_ID
ORDER BY EMP_NAME;

 

LEVEL EMPLOYEE                 EMP_ID     MGR_ID

----- -------------------- ---------- ----------

    4       ADAMS                7876       7788

    3     ALLEN                  7499       7698

    2   BLAKE                    7698       7839

    2   CLARK                    7782       7839

    3     FORD                   7902       7566

    3     JAMES                  7900       7698

    2   JONES                    7566       7839

    1 KING                       7839

    3     MARTIN                 7654       7698

    3     MILLER                 7934       7782

    3     SCOTT                  7788       7566

    4        SMITH               7369       7902

    3     TURNER                 7844       7698

    3     WARD                   7521       7698

Listagem 20.

 

Como você pode perceber, é impossível identificar a relação hierárquica entre as linhas. Para lidar com isto, utilizamos a palavra-chave SIBLINGS. Ela é utilizada em conjunto com uma cláusula ORDER BY para ordenar o conjunto de resultados apropriadamente. A Listagem 21 apresenta um exemplo deste cenário.

 

SELECT LEVEL, LPAD('  ',2*(LEVEL - 1)) || EMP_NAME "EMPLOYEE", EMP_ID, MGR_ID
FROM EMPLOYEE
START WITH EMP_ID = 7839
CONNECT BY PRIOR EMP_ID = MGR_ID
ORDER SIBLINGS BY EMP_NAME;

 

LEVEL EMPLOYEE                 EMP_ID     MGR_ID

----- -------------------- ---------- ----------

    1 KING                       7839

    2   BLAKE                    7698       7839

    3     ALLEN                  7499       7698

    3     JAMES                  7900       7698

    3     MARTIN                 7654       7698

    3     TURNER                 7844       7698

    3     WARD                   7521       7698

    2   CLARK                    7782       7839

    3     MILLER                 7934       7782

    2   JONES                    7566       7839

    3     FORD                   7902       7566

    4       SMITH                7369       7902

    3     SCOTT                  7788       7566

    4       ADAMS                7876       7788

Listagem 21.

 

Perceba que BLAKE, CLARK e JONES são irmãos hierárquicos e estão exibidos em ordem ascendente. Também são irmãos hierárquicos os descendentes de BLAKE – ALLEN, JAMES, MARTIN, TURNER e WARD.

Conclusão

Vimos neste artigo a utilização das cláusulas Start With e Connect By em comandos SQL no Oracle para a pesquisa de registros em tabelas que possuem auto-relacionamento. O Oracle 10g aprimora ainda mais os já avançados recursos de consulta hierárquica presentes na versão 9i do Oracle. Dentre os novos recursos, destacam-se as maneiras fáceis de identificar folhas e ciclos nos dados. A organização de linhas irmãs fornece uma excelente maneira de melhorar a legibilidade dos conjuntos de resultados. Os desenvolvedores já familiarizados com as construções hierárquicas do Oracle certamente acharão esses recursos muito úteis.

 
Você precisa estar logado para dar um feedback. Clique aqui para efetuar o login
Receba nossas novidades
Ficou com alguma dúvida?