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

Função isdigit no Postgres

Função isdigit no Postgres

Esta função escalar excelente para testar se um derterminado campo tem strings, letras ou apenas números em Postgres.

Retorna true para verdadeiro caso seja apenas números e false para falso, caso encontre textos dentro do campo.

Espero ter ajudado.

Segue fonte abaixo:

-- apagar se existir 
DROP FUNCTION IF EXISTS usf_isdigit(text);
CREATE FUNCTION usf_isdigit(text) RETURNS BOOLEAN AS
$$
--
-- Nome Artefato/Programa..: usf_isdigit.sql
-- Autor(es)...............: Emerson Hermann (emersonhermann [at] gmail.com) O Peregrino (http://www.emersonhermann.blogspot.com) adaptado de Manuel Sugawara em http://bytes.com/topic/postgresql/answers/174214-determine-if-string-digit
-- Data Inicio ............: 08/07/2011
-- Data Atualizacao........: 12/04/2018
-- Versao..................: 0.03
-- Compilador/Interpretador: PostgreSql
-- Sistemas Operacionais...: Linux/Windows
-- SGBD....................: Postgres 8.x/9.x/10.x
-- Kernel..................: Nao informado!
-- Finalidade..............: store procedure (user store function - usf) para testa se um campo eh digito ou nao, retorna true para verdadeiro e false para falso 
-- OBS.....................: 
--

SELECT CASE WHEN (array_upper(string_to_array($1, '.'),1) > 2 ) THEN 
                FALSE 
            ELSE 
                $1 ~ '^(-)?\.|[0-9]+$' 
            END AS usf_isdigit
/*
Exemplo: 
 
SELECT usf_isdigit('ISSO É UM VALOR NUMERICO?'); -- retorna false
SELECT usf_isdigit('ISSO É UM VALOR N.UMER.IC.O?'); -- retorna false
SELECT usf_isdigit('ISSO É UM VALOR N.UMERICO?'); -- retorna false
SELECT usf_isdigit('3000'); --retorna true
SELECT usf_isdigit('2700.00'); --retorno true 
SELECT usf_isdigit('2700.00.00'); --retorno false

*/ 
$$ 
LANGUAGE SQL;

Remover Letras em campos do Postgres

Remover Letras em campos do Postgres

Mesmo teretêtê, que foi publicado no artigo Remover Letras em campos do Postgres, publicado no blog O Peregrino (http://emersonhermann.blogspot.com) para o SGBD SQL Server, só que dessa vez para o SGBD Postgres ... :)

Vez por outra, precisamos de fazer uso de campos concatenados com números e strings e em alguns casos só procisamos
dos números nos joins ou em ETL para extração de dados.

Poderiamos simplesmente usar a function substring se o campo string fosse de tamanho fixo, e nesses casos a coisa complica.

A exemplo, um campo nome id_tabela de uma tabela qualquer, em algumas ocorrencias, isto é, linhas o id_tabela está ABCZ00001
e em outra ZCBA00002X, e uma outra linha também poderia está preenchida dessa forma ABZ00003.

Então sendo assim:

Aqui segue a dica, de uma function desenvolvida em PL/pgSQL que resolve esse problema:

--
-- Nome Artefato/Programa..: sp_rm_letters_pg.sql
-- Autor(es)...............: Emerson Hermann (emersonhermann at gmail.com) O Peregrino http://emersonhermann.blogspot.com
-- Data Inicio ............: 25/07/2011
-- Data Atual..............: 16/08/2011
-- Versao..................: 0.03
-- Compilador/Interpretador: PostgreSql
-- Sistemas Operacionais...: Linux/Windows
-- SGBD....................: PostgreSql 8.x/9.x
-- Kernel..................: Nao informado!
-- Finalidade..............: store procedure (function) para remever letras das strings
-- OBS.....................: 
--
-- DROP FUNCTION IF EXISTS sp_rm_letters(number text);
CREATE OR REPLACE FUNCTION sp_rm_letters(number text) RETURNS text AS
$$
DECLARE 
     c         integer;
     number_r  text;
BEGIN 
     c         := 65;
     number_r  := number;
     --até 126, isto é, mais 62, contempla os caracteres padrão ANSI original, até 255 (191) contempla o ASCII extendido .
     WHILE c<(65+62) LOOP
          number_r  := replace(number_r,chr(c),'');
          c         := c + 1;
     END LOOP;
     RETURN(number_r);
END;
$$
LANGUAGE 'plpgsql' STABLE CALLED ON NULL INPUT SECURITY INVOKER;

/*
-- Abaixo, letras, caracteres que serão removidos da string, pela function sp_rm_letters:
ABCDEFGHIJKLMNOPQRSTUVWXYZ[\]^_`abcdefghijklmnopqrstuvwxyz{|}~áàâãäéèêëíìïóòôõöúùûüÁÀÂÃÄÉÈÊËÍÌÏÓÒÔÕÖÚÙÛÜçÇ

-- Chamado a função 

SELECT sp_rm_letters('ABC123Z4') -- Retorno 1234

-- Ou

SELECT sp_rm_letters('éab~Z|||zcABC123éáúZ4'||chr(127)) -- Retorno 1234
 
-- Ou

SELECT sp_rm_letters('ABC123Z4'); -- Retorno 1234

-- A função sp_rm_letters não remove caracteres especiais mas pode ser combinada com a function sp_rm_special_chars, para remover caracteres especiais.
select sp_rm_special_chars(sp_rm_letters('éab~Z|||zcABC123éáúZ4z2'||chr(127)));

-- Uma implementação da função 
SELECT * FROM tabela1 t1 JOIN tabela2 t2 ON t1.id_campo=sp_rm_letters(t2.id_campo);
*/

Que DEUS abençoe o Postgres e quem trabalha com o mesmo!

Valeu!

Vida de DBA

Essa tirinha eu copiei do site: http://vidadeprogramador.com.br/
Muito bom!
Essa é a vida do DBA, é pura realidade, baseado em fatos reais.
:)

UPPER JOIN

kkkkkkkkkkkkkkkkkkkkkkkkkk

Função isdigit no SQL Server

Função isdigit no SQL Server

Esta função escalar excelente para testar se um derterminado campo tem strings, letras ou apenas números em SQL Server.

Retorna 1 para verdadeiro caso seja apenas números e 0 para falso, caso encontre textos dentro do campo.

Espero ter ajudado.

Segue fonte abaixo:

--
-- Nome Artefato/Programa..: sp_isdigit.sql
-- Autor(es)...............: Emerson Hermann (emersonhermann [at] gmail.com) O Peregrino (http://www.emersonhermann.blogspot.com) adaptado de http://www.simple-talk.com/sql/t-sql-programming/sql-string-user-function-workbench-part-1/#eighth
-- Data Inicio ............: 08/07/2011
-- Data Atualizacao........: 05/08/2011
-- Versao..................: 0.02
-- Compilador/Interpretador: T-SQL (Transact SQL) 
-- Sistemas Operacionais...: Windows
-- SGBD....................: MS SQL Server 2008
-- Kernel..................: Nao informado!
-- Finalidade..............: store procedure (function) para testa se um campo é digito ou não, retorna 1 para verdadeiro e 0 para falso 
-- OBS.....................: 
--

IF EXISTS (
            SELECT * 
              FROM sys.objects 
             WHERE object_id = OBJECT_ID(N'[dbo].[sp_isdigit]') 
               AND type IN (N'FN')
           )
 DROP FUNCTION sp_isdigit
GO

CREATE FUNCTION sp_isdigit (@string varchar(max))  

RETURNS INT
AS 
BEGIN
   RETURN 
   (  
     SELECT CASE WHEN PATINDEX('%[^0-9]%', @string) > 0 THEN 
       0
      ELSE 
       1
      END AS sp_isdigit
   )
END;
GO
/*
Exemplo: 

SELECT dbo.sp_isdigit('ISSO É UM VALOR NÚMERICO?'); -- 0 
SELECT dbo.sp_isdigit('3000'); --retorno 1
SELECT dbo.sp_isdigit('2700.00'); --retorno 0 
*/