Querys recursivas no banco de dados oracle

Querys Recursivas no Oracle

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

artefato/Programa..: querys_recursivas_no_oracle

-- Empresa.................:

-- 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;