Querys Recursivas no Oracle

Querys Recursivas no Oracle

Segue um exemplo prático de como fazer querys recursivas no Oracle, usando genealogia.

Recomendado para quem está estudando para a obtenção da Certificação Oracle Database: SQL Expert (OCE – Oracle Certified Expert), Exame 1Z0-047 Oracle Database SQL Expert.

--
-- Nome Artefato/Programa..: querys_recursivas_no_oracle 
-- Empresa.................: 
-- Autor(es)...............: Emerson Hermann (emersonhermann@gmail.com) 
-- Data Inicio ............: 07/04/2011
-- Data Atual..............: 22/07/2011
-- Versao..................: 0.01
-- Compilador/Interpretador: Oracle
-- Sistemas Operacionais...: Linux/Windows/Outros SOs
-- SGBD....................: Oracle 9i/10g/11g
-- Kernel..................: Nao informado!
-- Finalidade..............: uso de querys recursivas no oracle com com start with ... connect by ... 
-- ........................: 
-- OBS.....................: 
--


/* testando no oracle com start with ... connect by */

--DROP TABLE genealogia;
CREATE TABLE genealogia
(
    id_genealogia integer     PRIMARY KEY 
  , nome varchar2(25)         NOT NULL
  , id_genealogia_pai integer NULL --FOREIGN KEY fk_genealogia REFERENCES genealogia(id_genealogia)

);

--TRUNCATE TABLE genealogia;

SELECT * FROM genealogia;

INSERT INTO genealogia (id_genealogia, nome, id_genealogia_pai) VALUES (1,'ABRAÃO',NULL);
INSERT INTO genealogia (id_genealogia, nome, id_genealogia_pai) VALUES (2,'ISAC',1);
INSERT INTO genealogia (id_genealogia, nome, id_genealogia_pai) VALUES (3,'ESAÚ',2);
INSERT INTO genealogia (id_genealogia, nome, id_genealogia_pai) VALUES (4,'JACÓ',2);
INSERT INTO genealogia (id_genealogia, nome, id_genealogia_pai) VALUES (5,'RÚBEN',4);
INSERT INTO genealogia (id_genealogia, nome, id_genealogia_pai) VALUES (6,'SIMEÃO',4);
INSERT INTO genealogia (id_genealogia, nome, id_genealogia_pai) VALUES (7,'LEVI',4);
INSERT INTO genealogia (id_genealogia, nome, id_genealogia_pai) VALUES (8,'JUDÁ',4);
INSERT INTO genealogia (id_genealogia, nome, id_genealogia_pai) VALUES (9,'ISSACAR',4);
INSERT INTO genealogia (id_genealogia, nome, id_genealogia_pai) VALUES (10,'ZEBULON',4);
INSERT INTO genealogia (id_genealogia, nome, id_genealogia_pai) VALUES (11,'JOSÉ',4);
INSERT INTO genealogia (id_genealogia, nome, id_genealogia_pai) VALUES (12,'BENJAMIM',4);
INSERT INTO genealogia (id_genealogia, nome, id_genealogia_pai) VALUES (13,'DÃ',4);
INSERT INTO genealogia (id_genealogia, nome, id_genealogia_pai) VALUES (14,'NAFTALI',4);
INSERT INTO genealogia (id_genealogia, nome, id_genealogia_pai) VALUES (15,'GADE',4);
INSERT INTO genealogia (id_genealogia, nome, id_genealogia_pai) VALUES (16,'ASER',4);
INSERT INTO genealogia (id_genealogia, nome, id_genealogia_pai) VALUES (17,'DINÁ',4);
INSERT INTO genealogia (id_genealogia, nome, id_genealogia_pai) VALUES (18,'PEREZ',8);
INSERT INTO genealogia (id_genealogia, nome, id_genealogia_pai) VALUES (19,'ZERA',8);
INSERT INTO genealogia (id_genealogia, nome, id_genealogia_pai) VALUES (20,'ESRON',18);
INSERT INTO genealogia (id_genealogia, nome, id_genealogia_pai) VALUES (21,'ARÃO ',20);
INSERT INTO genealogia (id_genealogia, nome, id_genealogia_pai) VALUES (22,'AMINADABE',21);
INSERT INTO genealogia (id_genealogia, nome, id_genealogia_pai) VALUES (23,'NASSON',22);
INSERT INTO genealogia (id_genealogia, nome, id_genealogia_pai) VALUES (24,'SALMON',23);
INSERT INTO genealogia (id_genealogia, nome, id_genealogia_pai) VALUES (25,'BOAZ',24);
INSERT INTO genealogia (id_genealogia, nome, id_genealogia_pai) VALUES (26,'OBEDE',25);
INSERT INTO genealogia (id_genealogia, nome, id_genealogia_pai) VALUES (27,'JESSÉ',26);
INSERT INTO genealogia (id_genealogia, nome, id_genealogia_pai) VALUES (28,'DAVI',27);

SELECT * FROM genealogia;


--query 1, AUTO RELACIONAMENTO

            SELECT g1.nome
                 , g1.id_genealogia
                 , g2.id_genealogia_pai 
              FROM genealogia g1
         LEFT JOIN genealogia g2 
                ON g1.id_genealogia = g2.id_genealogia_pai 
                 ;   

--query 2, CONNECT BY PRIOR

            SELECT nome
                 , id_genealogia
                 , id_genealogia_pai 
              FROM genealogia
  CONNECT BY PRIOR id_genealogia = id_genealogia_pai
                 ;   

--query 3, LEVEL              

            SELECT nome
                 , id_genealogia
                 , id_genealogia_pai 
                 , LEVEL 
              FROM genealogia
  CONNECT BY PRIOR id_genealogia = id_genealogia_pai
                 ;   

--query 4,  START WITH

            SELECT nome
                 , id_genealogia
                 , id_genealogia_pai 
                 , LEVEL
              FROM genealogia
        START WITH id_genealogia = 4  --JACÓ
  CONNECT BY PRIOR id_genealogia = id_genealogia_pai
          ORDER BY LEVEL ASC
                 ;
                 
--query 5, COM ARVORE

            SELECT RPAD(LPAD(' ', 5*(LEVEL-1))||nome,30) AS arvore  
                 , nome
                 , id_genealogia
                 , id_genealogia_pai 
                 , LEVEL
              FROM genealogia
        START WITH id_genealogia = 1
  CONNECT BY PRIOR id_genealogia = id_genealogia_pai
          ORDER BY LEVEL ASC
                 ;

--query 6, SYS_CONNECT_BY_PATH

            SELECT LPAD(' ', 5*(LEVEL-1)) || nome AS representacao_arvore1
                 , SYS_CONNECT_BY_PATH(nome, '/') AS represencao_arvore2  
                 , nome
                 , id_genealogia
                 , id_genealogia_pai 
                 , LEVEL
              FROM genealogia
        START WITH id_genealogia = 1
  CONNECT BY PRIOR id_genealogia = id_genealogia_pai
          ORDER BY LEVEL ASC
                 ;

--query 7, ORDER SIBLINGS BY

            SELECT LPAD(' ', 5*(LEVEL-1)) || nome AS representacao_arvore1
                 , SYS_CONNECT_BY_PATH(nome, '/') AS represencao_arvore2  
                 , nome
                 , id_genealogia
                 , id_genealogia_pai 
                 , LEVEL
              FROM genealogia
        START WITH id_genealogia = 1
  CONNECT BY PRIOR id_genealogia = id_genealogia_pai
 ORDER SIBLINGS BY nome ASC
                 ;
                 
--query 8, CONNECT_BY_ROOT

CREATE OR REPLACE  VIEW  vw_genealogia  AS 
            SELECT LPAD('>', 5*(LEVEL-1)) || nome AS representacao_arvore1
                 , SYS_CONNECT_BY_PATH(nome, '\') AS represencao_arvore2  
                 , CONNECT_BY_ROOT nome AS raiz 
                 , nome
                 , id_genealogia
                 , id_genealogia_pai 
                 , LEVEL AS nivel 
              FROM genealogia
        START WITH id_genealogia = 1
--CONNECT BY NOCYCLE PRIOR  id_genealogia = id_genealogia_pai
  CONNECT BY PRIOR id_genealogia = id_genealogia_pai
 --ORDER SIBLINGS BY level  ASC
ORDER  BY level  ASC;

SELECT * FROM vw_genealogia;

Sem stress...