Banco de Dados

Ξ Deixe um comentário

Oracle – Convertendo linhas em colunas com expressões regulares

publicado por Paulo Planez

Figura - Oracle - Convertendo linhas em colunas com expressões regularesAs expressões regulares fazem parte dos recursos computacionais há algum tempo. O fato de eu atuar fortemente com sistemas financeiros fez com que, por algum tempo, eu desdenhasse tal recurso por nunca ter visto uma aplicação prática para ele. Porém, uma necessidade em especial me motivou a utilizá-lo. Imagine o seguinte cenário:

Eu tenho tabelas no Oracle que contém os dias da semana em que uma determinada cobrança deve ser gerada, que possui o seguinte formato: 2;4;6. Isso significa que esta cobrança deve ser gerada nas segundas, quartas e sextas-feiras

Para que eu possa gerar a cobrança no dia correto eu preciso saber se o dia de hoje se encontra em um dos dias listados. O script abaixo é uma simulação do cenário no Oracle:

set serveroutput on
DECLARE
   --------------------------------------------------
   --> Array para simular uma tabela
   --------------------------------------------------
   type r_dias is record (dias varchar2(10));
   type t_dias is table of r_dias index by binary_integer;
   v_dias t_dias;
   --------------------------------------------------
   --> Dia corrente
   --------------------------------------------------
   v_hoje varchar2(1) := TO_CHAR(sysdate,'D');
BEGIN
   --------------------------------------------------
   --> Alimentando o array
   --------------------------------------------------
   v_dias(1).dias := '2;4;6';
   v_dias(2).dias := '3;5';
   v_dias(3).dias := '2;3;4;5;6';
   --------------------------------------------------
   --> Processando o array
   --------------------------------------------------
   DBMS_OUTPUT.put_line ('Imprimindo registros a processars');
   FOR x IN 1..V_DIAS.count LOOP
      DBMS_OUTPUT.put_line ('Dias da semana: '||V_DIAS(x).dias);
      --> SE o dia de hoje estiver no dia listado
      -->    Gerar a cobrança
      --> FIM SE
   END LOOP;
END;
/

O requisito exige que, se o dia corrente estiver em um dos dias listados, a cobrança para o estabelecimento deve ser gerada. Porém, como saber se o dia de hoje se encaixa na lista de dias válidos?

Existem várias maneiras de fazer isso porém o objetivo aqui é solucionar este problema utilizando-se do recurso de expressões regulares, portanto, vamos a ela:

O primeiro passo é criar uma expressão regular que permita separar os registros. Execute o script abaixo:

set serveroutput on;
DECLARE
   v_dias varchar2(10) := '2;4;6';
BEGIN
   DBMS_OUTPUT.put_line ('Primeira Posição: '||REGEXP_SUBSTR(v_dias,'[^;]+',1,1));
   DBMS_OUTPUT.put_line ('Segunda Posição.: '||REGEXP_SUBSTR(v_dias,'[^;]+',1,2));
   DBMS_OUTPUT.put_line ('Terceira Posição: '||REGEXP_SUBSTR(v_dias,'[^;]+',1,3));
END;
/

O segundo passo é criar uma expressão que permita de forma automática qualificar a posição da informação desejada e converter linhas em colunas. Execute o script abaixo várias vezes com diferentes separadores como Ponto e Vírgula(;), ponto (.), vírgula(,) e pipe(|) para ver as consequências:

DECLARE
   v_dias varchar2(100) := '&dias_separados_ponto_e_virgula';
BEGIN
   FOR x IN (select REGEXP_SUBSTR(str, exp, 1, level) dias_validos
               from (select v_dias   str 
                          , '[^;]+'  exp
                       from dual)
             connect by REGEXP_SUBSTR(str, exp, 1, level) is not null)  LOOP
      DBMS_OUTPUT.put_line (X.dias_validos);
   END LOOP;
END;
/

Desta forma temos a linha convertida em coluna utilizando-se de uma expressão regular a um custo baixo. A partir dai basta adicionar a lógica para geração da cobrança:

set serveroutput on
DECLARE
   --------------------------------------------------
   --> Array para simular uma tabela
   --------------------------------------------------
   type r_dias is record (dias varchar2(20));
   type t_dias is table of r_dias index by binary_integer;
   v_dias t_dias;
   --------------------------------------------------
   --> Dia corrente
   --------------------------------------------------
   v_hoje varchar2(1) := TO_CHAR(sysdate,'D');
BEGIN
   --------------------------------------------------
   --> Alimentando o array
   --------------------------------------------------
   v_dias(1).dias := '2;4;6';         --> Cenário 1
   v_dias(2).dias := '1;3;5';         --> Cenário 2
   v_dias(3).dias := '3;5;7';         --> Cenário 3
   v_dias(4).dias := '1;2;3;4;5;6;7'; --> Cenário 4
   --------------------------------------------------
   --> Processando o array
   --------------------------------------------------
   FOR x IN 1..V_DIAS.count LOOP
      -----------------------------------------------
      --> Convertendo linhas em colunas
      -----------------------------------------------
      FOR y IN (select REGEXP_SUBSTR(str, exp, 1, level) dias_validos
                  from (select V_DIAS(x).dias str 
                             , '[^;]+'        exp
                          from dual)
                connect by REGEXP_SUBSTR(str, exp, 1, level) is not null)  LOOP
         --------------------------------------------
         --> Checando o dia
         --------------------------------------------
         IF Y.dias_validos = v_hoje THEN
            DBMS_OUTPUT.put_line ('Para o cenário '||x||' Seria gerada cobrança hoje');
         END IF;
      END LOOP;
   END LOOP;
END;
/

O grande benefício dessa técnica é converter uma linha delimitada por um separador em colunas a um custo baixo, conforme abaixo:

select REGEXP_SUBSTR(str, exp, 1, level) dias_validos
   from (select '1;2;3;4;5;6;7' str 
              , '[^;]+'        exp
           from dual)
connect by REGEXP_SUBSTR(str, exp, 1, level) is not null
-----------------------------------------------------------------------------
| Id  | Operation                    | Name | Rows  | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |      |     1 |     2   (0)| 00:00:01 |
|*  1 |  CONNECT BY WITHOUT FILTERING|      |       |            |          |
|   2 |   FAST DUAL                  |      |     1 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------

Estatística
----------------------------------------------------------
  1  recursive calls
  0  db block gets
  0  consistent gets
  0  physical reads
  0  redo size
406  bytes sent via SQL*Net to client
346  bytes received via SQL*Net from client
  2  SQL*Net roundtrips to/from client
  1  sorts (memory)
  0  sorts (disk)
  7  rows processed

Autor

Graduado em administração com especialização em Finanças, atua desde 1990 com sistemas de informação, em sua maioria focado em sistemas de gestão financeira. ♪ Atuou com sistemas de informação proprietários dos mais variados como Sistemas de gestão de Grãos, Sistemas de controle financeiro para transações eletrônicas e, no segmento de governo, com Sistemas Financeiros e de Sanidade Animal e Vegetal e com sistemas para processamento de Malha Fiscal. ♪ Atua por mais de 15 anos com sistemas de gestão (ERP) cobrindo ciclos operacionais como "Quote to Cash" e "Purchase to Pay" utilizando software da Oracle Corporation (Oracle EBS) em empresas como Globo, Motorola, Alcoa e Dell. ♪ Possui como linha de estudo e pesquisa a economicidade dos sistemas de informação, de modo a extrair destes o máximo de benefícios com o mínimo de recursos.♪ Contato: paulo.planez@gmail.com

Paulo Planez

Comentários

You must be logged in to post a comment.

Busca

Patrocínio

Publicidade



Siga-nos!

Newsletter: Inscreva-se

Para se inscrever em nossa newsletter preencha o formulário.

Artigos Recentes