Usando CONNECT BY

O CONNECT BY é um comando muito interessante da linguagem PL/SQL, pois ele permite a criação uma árvore hierárquica entre as tuplas de uma consulta, definindo o registro pai e o registro filho.

Considere a tabela abaixo:



select * from
(select 5533 registro, 'João' nome, 5000 registro_pai from dual
union
select 5000 registro, 'Lima' nome, NULL registro_pai from dual
union
select 4444 registro, 'Zé' nome, 5533 registro_pai from dual
union
select 3358 registro, 'Souza' nome, 4444 registro_pai from dual) tabela;

REGISTRO	NOME	REGISTRO_PAI
3358	Souza	4444
4444	Zé	5533
5000	Lima
5533	João	5000

A consulta a seguir monta a árvore hierarquica para cada um dos registros em ordem crescente do menor filho para o maior pai:


select * from
(select 5533 registro, 'João' nome, 5000 registro_pai from dual
union
select 5000 registro, 'Lima' nome, NULL registro_pai from dual
union
select 4444 registro, 'Zé' nome, 5533 registro_pai from dual
union
select 3358 registro, 'Souza' nome, 4444 registro_pai from dual) tabela
connect by prior registro_pai = registro;


Resultado:

REGISTRO	NOME	REGISTRO_PAI
3358	Souza	4444
4444	Zé	5533
5533	João	5000
5000	Lima
4444	Zé	5533
5533	João	5000
5000	Lima
5000	Lima
5533	João	5000
5000	Lima

Se invertermos a cláusula CONNECT BY a ordem dos hierarquia é alterada para cada registros, passando a ser ordenado do maior pai para o menor filho:

select * from

(select 5533 registro, 'João' nome, 5000 registro_pai from dual
union
select 5000 registro, 'Lima' nome, NULL registro_pai from dual
union
select 4444 registro, 'Zé' nome, 5533 registro_pai from dual
union
select 3358 registro, 'Souza' nome, 4444 registro_pai from dual) tabela
connect by prior registro = registro_pai;


Resultado:

REGISTRO	NOME	REGISTRO_PAI
3358	Souza	4444
5533	João	5000
4444	Zé	5533
3358	Souza	4444
4444	Zé	5533
3358	Souza	4444
5000	Lima
5533	João	5000
4444	Zé	5533
3358	Souza	4444

Se incluírmos a cláusula START WITH podemos definir qual a árvore deve ser exibida excluindo todas as outras da consulta:


select * from
(select 5533 registro, 'João' nome, 5000 registro_pai from dual
union
select 5000 registro, 'Lima' nome, NULL registro_pai from dual
union
select 4444 registro, 'Zé' nome, 5533 registro_pai from dual
union
select 3358 registro, 'Souza' nome, 4444 registro_pai from dual) tabela
connect by prior registro = registro_pai
start with nome = 'Lima';


Resultado:

REGISTRO	NOME	REGISTRO_PAI
5000	Lima
5533	João	5000
4444	Zé	5533
3358	Souza	4444

Outra caracteristcia muito interessante desse comando é que ele permite a construção de uma estrutura de informações como podem ser observado nas consultas abaixo:

select level 
from dual 
connect by level <= 10;


Essa consulta retorna os números menor e igual a 10...

LEVEL
1
2
3
4
5
6
7
8
9
10

select level - 1 + TO_DATE('01/03/2013','DD/MM/RRRR') dias 
from dual 
connect by level <= last_day(TO_DATE('01/03/2013','DD/MM/RRRR')) - TO_DATE('01/03/2013','DD/MM/RRRR') + 1;


Essa consulta exibe uma estrutura com todos os dias de um determinado mês...

DIAS

01/03/2013

02/03/2013

03/03/2013

04/03/2013

05/03/2013

06/03/2013

07/03/2013

08/03/2013

09/03/2013

10/03/2013

11/03/2013

12/03/2013

13/03/2013

14/03/2013

15/03/2013

16/03/2013

17/03/2013

18/03/2013

19/03/2013

20/03/2013

21/03/2013

22/03/2013

23/03/2013

24/03/2013

25/03/2013

26/03/2013

27/03/2013

28/03/2013

29/03/2013

30/03/2013

31/03/2013

Espero ter contribuído como novos conhecimentos!

Em breve novas dicas ...

Dicas de Funções Genéricas

Esta função bem simples retorna a quantidade de um determinado dia da semana existente em um intervalo de datas passados como parâmetro:



CREATE OR REPLACE FUNCTION QTTY_DAY_WEEK(p_dt_start DATE, p_dt_end DATE, p_day_week NUMBER) RETURN NUMBER IS

  v_day NUMBER;
  v_count NUMBER := 0;
  v_start NUMBER;
  v_end NUMBER;
 

BEGIN

  v_start := TRUNC(SYSDATE) - p_dt_start;
  v_end := TRUNC(SYSDATE) - p_dt_end;

  FOR v_aux IN v_end .. v_start LOOP

    SELECT to_char(SYSDATE - v_aux ,'d')
      INTO v_day
      FROM dual;

    IF v_day IN (p_day_week) THEN -- 1 is Sunday, 2 is Monday, ...
       v_count:= v_count + 1;
    END IF;

  ENd LOOP;
 
  RETURN(v_count);

END;


onde:

p_dt_start é a data inicial
p_dt_end é a data final
p_day_week é o dia da semana (1 para Domingo, 2 para Segunda, 3 para Terça, ..., 7 para Sábado)

para testar:


select QTTY_DAY_WEEK(TO_DATE('01/03/2013','DD/MM/RRRR'), TO_DATE('20/03/2013','DD/MM/RRRR'), 1) QTD_DOMINGOS
from dual;

Resultado:
3

Até a próxima postagem...