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_VIEWCOD_ITEM_PAI_TREE_VIEWLEVELDESCRICAOURLDATA_INCLUSAO
0
null
1
SITES FAVORITOShttp://www.furutani.eti.br21-Feb-08
13

null
1
IMPORTANTEShttp://www.furutani.com.br21-Feb-08
1
0
2
BUSCADOREShttp://www.furutani.eti.br21-Feb-08
5
0
2
PORTAIShttp://www.furutani.eti.br21-Feb-08
15
13
2
GLOBOhttp://www.globo.com21-Feb-08
14
13
2
GOOGLEhttp://www.google.com.br21-Feb-08
9
0
2
BLOGShttp://www.furutani.eti.br/blog21-Feb-08
2
1
3
GOOGLEhttp://www.google.com.br21-Feb-08
7
5
3
TERRAhttp://www.terra.com.br21-Feb-08
11
9
3
TESTEhttp://www.TESTE.COM21-Feb-08
12
9
3
BLOGGERhttp://www.blogger.com21-Feb-08
10
9
3
MEUhttp://www.robertofurutani.com21-Feb-08
8
5
3
GLOBOhttp://www.globo.com21-Feb-08
6
5
3
UOLhttp://www.uol.com.br21-Feb-08
3
1
3
CADEhttp://www.CADE.com.br21-Feb-08
4
1
3
MSNhttp://www.MSN.com.br21-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

Share


Um comentário

  1. #
    Ruben Badaró
    fevereiro 22nd, 2008 at 12:59

    Também escrevi sobre o mesmo tema e o teu post está um pouco mais aprofundado.
    Bom post 😉

    Responda a esse comentário

Deixe um comentário