Oracle: Obtendo dados hierarquicamente

Mais um grande ajuda provida pelo Oracle, é a possibilidade de trazer os dados de forma hierárquica. Para isso, usamos o comando START WITH ... CONNECT BY PRIOR ..., que funciona da seguinte forma [1]:

for rec in (select * from some_table) loop
  if FULLFILLS_START_WITH_CONDITION(rec) then
    RECURSE(rec, rec.child);
  end if;
end loop;
 
procedure RECURSE (rec in MATCHES_SELECT_STMT, parent_id IN field_type) is
  begin
  APPEND_RESULT_LIST(rec);     
  for rec_recurse in (select * from some_table) loop
    if FULLFILLS_CONNECT_BY_CONDITION(rec_recurse.id, parent_id) then
      RECURSE(rec_recurse,rec_recurse.id);
    end if;
  end loop;
end procedure RECURSE;

Para demonstrar facilmente, vamos aplicá-lo em uma tabela usada pelo Oracle para inserir os dados de um comando EXPLAIN PLAN.

TRUNCATE TABLE PLAN_TABLE;
 
-- Gerando os dados do explain
EXPLAIN PLAN FOR
SELECT *
  FROM T_MEDICOS A,
       T_AGENDA B,
       T_HORARIO_AGENDA C
  WHERE A.NR_MEDICO = B.NR_MEDICO
    AND B.ID_AGENDA = C.ID_AGENDA;
 
-- Para mostrar o explain formatado
SELECT LPAD(' ', (LEVEL-1)*2) || OPERATION, NVL2(OPTIONS, '('||OPTIONS||')', ''),  OBJECT_NAME 
  FROM PLAN_TABLE
  START WITH ID = 0
  CONNECT BY PRIOR ID = PARENT_ID;

Como pode ver, na tabela PLAN_TABLE podemos traçar a ligação de um registro com um outro pelo respectivo ID e um PARENT_ID (como é de costume), então assim o Oracle trará os dados hierarquicamente segundo a condição dada no comando recursivamente (como apresentado acima).

A saída formatada do exemplo será:

SELECT STATEMENT		
  HASH JOIN		
    HASH JOIN		
      TABLE ACCESS	(FULL)	T_MEDICOS
      TABLE ACCESS	(FULL)	T_AGENDA
    TABLE ACCESS	(FULL)	T_HORARIO_AGENDA

Simples assim! :-)

[1] - http://www.adp-gmbh.ch/ora/sql/connect_by.html

No link acima há outros exemplos e cita também features adicionadas na versão 9i.

Meu coração até bateu

Meu coração até bateu mais forte quando eu conheci esse recurso hahah

Excelente dica!

Comment viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.

Post new comment

The content of this field is kept private and will not be shown publicly.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Allowed HTML tags: <a> <em> <strong> <cite> <code> <ul> <ol> <li> <dl> <dt> <dd>
  • Lines and paragraphs break automatically.

More information about formatting options