quinta-feira, 13 de junho de 2013

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;


Nenhum comentário:

Postar um comentário