Queries hierarquicas no Oracle

Olá

O Oracle tem uma função muito interessante chamada connect by.
Com essa função você pode criar queries hierarquicas, muito útil para montar menus, principalmente os menus do tipo árvore (Tree view).

Sintaxe:

START WITH  CONNECT BY [NOCYCLE] 

ou

CONNECT BY [NOCYCLE]  START WITH 

Exemplo:
Eu tenho uma tabela com a seguinte estrutura:

create table TAB_TREE_VIEW(
COD_ITEM_TREE_VIEW       NUMBER not null,
COD_ITEM_PAI_TREE_VIEW   NUMBER,
DESCRICAO                VARCHAR2(20),
URL                      VARCHAR2(100),
DATA_INCLUSAO            DATE not null);

Nessa tabela existe um auto-relacionamento, para adicionar um registro o valor da coluna COD_ITEM_PAI_TREE_VIEW deve existir na coluna COD_ITEM_TREE_VIEW.

Abaixo os constraints da tabela, observe a foreign key:

alter table TAB_TREE_VIEW
add constraint PK_TAB_TREE_VIEW primary key (COD_ITEM_TREE_VIEW)
using index
...
alter table TAB_TREE_VIEW
add constraint FK_TAB_TREE_VIEW foreign key (COD_ITEM_TREE_VIEW)
references TAB_TREE_VIEW (COD_ITEM_TREE_VIEW) on delete cascade;

Depois de inserir alguns dados para testes, eu criei a seguinte consulta:

select cod_item_tree_view,
cod_item_pai_tree_view,
level,
descricao,
url,
data_inclusao
from   tab_tree_view
start with cod_item_pai_tree_view is null
connect by prior cod_item_tree_view = cod_item_pai_tree_view
order by level

E ela me retornou:

COD_ITEM_TREE_VIEW
COD_ITEM_PAI_TREE_VIEW
LEVEL
DESCRICAO
URL
DATA_INCLUSAO
0

null
1
SITES FAVORITOS
http://www.furutani.eti.br
21-Feb-08
13

null
1
IMPORTANTES
http://www.furutani.com.br
21-Feb-08
1
0
2
BUSCADORES
http://www.furutani.eti.br
21-Feb-08
5
0
2
PORTAIS
http://www.furutani.eti.br
21-Feb-08
15
13
2
GLOBO
http://www.globo.com
21-Feb-08
14
13
2
GOOGLE
http://www.google.com.br
21-Feb-08
9
0
2
BLOGS
http://www.furutani.eti.br/blog
21-Feb-08
2
1
3
GOOGLE
http://www.google.com.br
21-Feb-08
7
5
3
TERRA
http://www.terra.com.br
21-Feb-08
11
9
3
TESTE
http://www.TESTE.COM
21-Feb-08
12
9
3
BLOGGER
http://www.blogger.com
21-Feb-08
10
9
3
MEU
http://www.robertofurutani.com
21-Feb-08
8
5
3
GLOBO
http://www.globo.com
21-Feb-08
6
5
3
UOL
http://www.uol.com.br
21-Feb-08
3
1
3
CADE
http://www.CADE.com.br
21-Feb-08
4
1
3
MSN
http://www.MSN.com.br
21-Feb-08

O que tem de diferente nessa query?

1. A coluna level – Isso é uma pseudo-coluna que indica qual o nível de hierarquia do registro;
2. A condição start with cod_item_pai_tree_view is null – Especifica a condição de inicio da hierarquia, nesse caso a hierarquica inicia-se com os registros onde cod_item_pai_tree_view é null;
3. A condição connect by prior cod_item_tree_view = cod_item_pai_tree_view – Especifica a condição de ligação entre pais e filhos. Nesse caso o cod_item_pai_tree_view indica de quem o registro é filho.

Abaixo descrevo as pseudo-colunas e operadores relacionados com connect by.

LEVEL – Retorna 1 se é raiz, 2 se é filho da raiz, 3 se é filho o filho da raiz, e assim por diante;
CONNECT_BY_ISLEAF – Retorna 1 se é a ponta da hierarquia, 0 caso contrário;
CONNECT_BY_ISCYCLE – Retorna 1 se o registro tem um filho que também é seu pai.
ORDER SIBLINGS BY – Ordena os filhos de um mesmo pai;
CONNECT_BY_ROOT – Retorna o valor da coluna do pai da linha;
SYS_CONNECT_BY_PATH – Retorna o caminho para a linha atual com o separador informado.

Mais informações acesse: Hierarchical Queries
http://www.psoug.org/reference/connectby.html

Até mais