Pular para o conteúdo

Número por extenso monetário e Cálculo de Fórmulas Matemáticas no Oracle

Número por extenso monetário e Cálculo de Fórmulas Matemáticas no Oracle

Olá,

Nesse artigo, mostrarei o uso de uma função que recebe como parâmetro um número real com o objetivo de retornar o número por extenso (monetário), e também uma função na qual se é possível obter o resultado de uma fórmula matemática, onde a mesma pode conter uma chamada para outras fórmulas.

Função extenso em PL/SQL

create or replace function extenso (valor number) return varchar2 is
         extenso varchar2(240);
         b1 number(1);
         b2 number(1);
         b3 number(1);
         b4 number(1);
         b5 number(1);
         b6 number(1);
         b7 number(1);
         b8 number(1);
         b9 number(1);
         b10 number(1);
         b11 number(1);
         b12 number(1);
         b13 number(1); 
         b14 number(1);
         l1 varchar2(12);
         l2 varchar2(3);
         l3 varchar2(9);
         l4 varchar2(3);
         l5 varchar2(6);
         l6 varchar2(8);
         l7 varchar2(12);
         l8 varchar2(3);
         l9 varchar2(9);
         l10 varchar2(3);
         l11 varchar2(6); 
         l12 varchar2(8);
         l13 varchar2(12);
         l14 varchar2(3);
         l15 varchar2(9);
         l16 varchar2(3);
         l17 varchar2(6);
         l18 varchar2(8);
         l19 varchar2(12);
         l20 varchar2(3);
         l21 varchar2(9);
         l22 varchar2(3);
         l23 varchar2(6);
         l24 varchar2(16);
         l25 varchar2(3);
         l26 varchar2(9);
         l27 varchar2(3);
         l28 varchar2(6);
         l29 varchar2(17);
         virgula_bi char(3);
         virgula_mi char(3);
         virgula_mil char(3);
         virgula_cr char(3);
         valor1 char(14);

 -- TABELA DE CENTENAS --
         centenas char(108) := '       Cento    Duzentos   Trezentos'||                               'Quatrocentos  Quinhentos  Seiscentos'||                               '  Setecentos  Oitocentos  Novecentos';

 -- TABELA DE DEZENAS --
         dezenas char(79)   := '      Dez    Vinte   Trinta Quarenta'||                               'Cinquenta Sessenta  Setenta  Oitenta'||                               'Noventa';

 -- TABELA DE UNIDADES --
         unidades char(54)  := '    Um  Dois  TresQuatro Cinco  Seis'||                               '  Sete  Oito  Nove';

 -- TABELA DE UNIDADES DA DEZENA 10 --
         unid10   char(81)  := '     Onze     Doze    Treze Quatorze'||                               '   QuinzeDezesseisDezessete  Dezoito'||                               ' Dezenove';

begin
  valor1 := lpad(to_char(valor*100), 14,'0');
  b1 := substr(valor1, 1, 1);
  b2 := substr(valor1, 2, 1);
  b3 := substr(valor1, 3, 1);
  b4 := substr(valor1, 4, 1);
  b5 := substr(valor1, 5, 1);
  b6 := substr(valor1, 6, 1);
  b7 := substr(valor1, 7, 1);
  b8 := substr(valor1, 8, 1);
  b9 := substr(valor1, 9, 1);
  b10 := substr(valor1, 10, 1);
  b11 := substr(valor1, 11, 1);
  b12 := substr(valor1, 12, 1);
  b13 := substr(valor1, 13, 1);
  b14 := substr(valor1, 14, 1);

  if valor != 0 then
     if b1 != 0 then
        if b1 = 1 then
           if b2 = 0 and b3 = 0 then
              l5 :=  'Cem';
           else
              l1 := substr(centenas, b1*12-11, 12);
           end if;
        else
           l1 := substr(centenas, b1*12-11, 12);
        end if;
     end if;
     if b2 != 0 then
        if b2 = 1 then
           if b3 = 0 then
              l5 :=  'Dez';
           else
              l3 :=  substr(unid10, b3*9-8, 9);
           end if;
        else
           l3 :=  substr(dezenas, b2*9-8, 9);
        end if;
     end if;
     if b3 != 0 then
        if b2 != 1 then
           l5 :=  substr(unidades, b3*6-5, 6);
        end if;
     end if;
     if b1 != 0 or b2 != 0  or b3 != 0 then
        if (b1 = 0 and b2 = 0) and b3 = 1 then
           l5 :=  'Hum';
           l6 :=  ' Bilhão';
        else
           l6 :=  ' Bilhões';
        end if;
        if valor > 999999999 then
           virgula_bi := ' e ';
           if (b4+b5+b6+b7+b8+b9+b10+b11+b12) = 0 then
              virgula_bi := ' de' ; 
           end if;
        end if;
        l1 :=  ltrim(l1);
        l3 :=  ltrim(l3);
        l5 :=  ltrim(l5);
        if b2 > 1 and b3 > 0 then
           l4 := ' e ';
        end if; 
        if b1 != 0 and (b2 !=0 or b3 != 0) then
           l2 := ' e ';
        end if;
     end if;
 -- ROTINA DOS MILHOES --
   if b4 != 0 then
      if b4 = 1 then
         if b5 = 0 and b6 = 0 then
            l7 :=  'Cem';
         else
            l7 := substr(centenas, b4*12-11, 12);
         end if;
      else
         l7 := substr(centenas, b4*12-11, 12);
      end if;
   end if;
   if b5 != 0 then
      if b5 = 1 then
         if b6 = 0 then
            l11 :=  'Dez';
         else
            l9 :=  substr(unid10, b6*9-8, 9);
         end if;
      else
         l9 :=  substr(dezenas, b5*9-8, 9);
      end if;
   end if;
   if b6 != 0 then
      if b5 != 1 then
         l11 :=  substr(unidades, b6*6-5, 6);
      end if;
   end if;
   if b4 != 0 or b5 != 0  or b6 != 0 then
      if (b4 = 0 and b5 = 0) and b6 = 1 then
         l11 :=  ' Hum';
         l12 :=  ' Milhão';
      else
         l12 :=  ' Milhões';
      end if;
      if valor > 999999 then
         virgula_mi := ' e ';
         if (b7+b8+b9+b10+b11+b12) = 0 then
            virgula_mi := ' de';
         end if;
      end if;
      l7 :=  ltrim(l7);
      l9 :=  ltrim(l9);
      l11 := ltrim(l11);
      if b5 > 1 and b6 > 0 then
         l10 := ' e ';
      end if;
      if b4 != 0 and (b5 !=0 or b6 != 0) then
         l8 := ' e ';
      end if;
   end if;
 -- ROTINA DOS MILHARES --
   if b7 != 0 then
      if b7 = 1 then
         if b8 = 0 and b9 = 0 then
            l17 :=  'Cem';
         else
            l13 := substr(centenas, b7*12-11, 12);
         end if;
      else
         l13 := substr(centenas, b7*12-11, 12);
      end if;
   end if;
   if b8 != 0 then
      if b8 = 1 then
         if b9 = 0 then
            l17 :=  'Dez';
         else
            l15 :=  substr(unid10, b9*9-8, 9);
         end if;
      else
         l15 :=  substr(dezenas, b8*9-8, 9);
      end if;
   end if;
   if b9 != 0 then
      if b8 != 1 then
         l17 :=  substr(unidades, b9*6-5, 6);
      end if;
   end if;
   if b7 != 0 or b8 != 0  or b9 != 0 then
      if (b7 = 0 and b8 = 0) and b9 = 1 then
         l17 :=  'Hum';
         l18 :=  ' Mil';
      else
         l18 :=  ' Mil';
      end if;
      if valor > 999 and (b10+b11+b12) !=0 then
         virgula_mil  := ' e ';
      end if;
      l13 :=  ltrim(l13);
      l15 :=  ltrim(l15);
      l17 :=  ltrim(l17);
      if b8 > 1 and b9 > 0 then
         l16 := ' e ';
      end if;
      if b7 != 0 and (b8 !=0 or b9 != 0) then
         l14 := ' e ';
      end if;
   end if;
 -- ROTINA DOS REAIS --
   if b10 != 0 then
     if b10 = 1 then
        if b11 = 0 and b12 = 0 then
           l19 :=  'Cem';
        else
           l19 := substr(centenas, b10*12-11, 12);
        end if;
     else
        l19 := substr(centenas, b10*12-11, 12);
     end if;
   end if;
     if b11 != 0 then
        if b11 = 1 then
           if b12 = 0 then
              l23 :=  'Dez';
           else
              l21 :=  substr(unid10, b12*9-8, 9);
           end if;
        else
           l21 :=  substr(dezenas, b11*9-8, 9);
        end if;
     end if;
     if b12 != 0 then
        if b11 != 1 then
           l23 :=  substr(unidades, b12*6-5, 6);
        end if;
     end if;
      if b10 != 0 or b11 != 0  or b12 != 0 then
         if valor > 0 and valor < 2 then
           l23 :=  'Hum';
        end if;
        l19 :=  ltrim(l19);
        l21 :=  ltrim(l21);
        l23 := ltrim(l23);
        if b11 > 1 and b12 > 0 then
           l22 := ' e ';
        end if;
        if b10 != 0 and (b11 !=0 or b12 != 0) then
              l20 := ' e ';
        end if;
      end if;
        if valor > 0 and valor < 2  then
           if b12!=0 then
             l24 := ' Real';
           end if;
        else
          if valor > 1 then
            l24 := ' Reais';
          end if;
        end if;
 -- TRATA CENTAVOS --
   if b13 != 0 OR b14 != 0 then
     if valor > 0 then
       if (b12 != 0) or (b1+b2+b3+b4+b5+b6+b7+b8+b9+b10+b11+b12)!=0 then
        L25 := ' e ';
       end if;
     end if;
     if b13 != 0 then
        if b13 = 1 then
           if b14 = 0 then
              l28 :=  'Dez';
           else
              l26 :=  substr(unid10, b14*9-8, 9);
           end if;
        else
           l26 :=  substr(dezenas, b13*9-8, 9);
        end if;
     end if;
     if b14 != 0 then
        if b13 != 1 then
           l28 :=  substr(unidades, b14*6-5, 6);
        end if;
     end if;
     if b13 != 0  or b14 != 0 then
        if valor = 1 then
           l28 :=  'Hum';
        end if;
        l26 :=  ltrim(l26);
        l28 := ltrim(l28);
        if b13 > 1 and b14 > 0 then
          l27 := ' e ';
        end if;
     end if;
     if (b1+b2+b3+b4+b5+b6+b7+b8+b9+b10+b11+b12) > 0     then
        if b13 = 0 and b14 = 1 then
           l29 := ' Centavo';
        else
           l29 := ' Centavos';
        end if;
     else
        if b13 = 0 and b14 = 1 then
           l29 := ' Centavo de Real';
        else
           l29 := ' Centavos de Real';
        end if;
      end if;
   end if;
 -- CONCATENAR O LITERAL --
     if l29 = ' Centavo de Real' or l29 = ' Centavos de Real' then
       virgula_mil := '';
     end if;
     extenso := l1||l2||l3||l4||l5||l6||virgula_bi
                ||L7||L8||L9||L10||L11||l12||virgula_mi
                ||l13||l14||l15||l16||l17||l18||virgula_mil
                ||L19||L20||L21||L22||L23||l24||virgula_cr
                ||L25||L26||L27||L28||L29;
     extenso := ltrim(extenso);
     extenso := replace(extenso,'  ',' ');
 else
     extenso := 'Zero';
 end if;
 return extenso;
 end;
 /

Depois da criação da função de banco de dados acima, mostrarei alguns exemplos de seu uso. Vale a pena salientar que a mesma funciona até o valor 999.999.999.999,99 como demonstrado abaixo.

SQL> select extenso(999999999999.99) from dual;

EXTENSO(999999999999.99)
------------------------------------------------------------------------------
Novecentos e Noventa e Nove Bilhões, Novecentos e  Noventa e Nove Milhões,
Novecentos e Noventa e Nove Mil, Novecentos e Noventa e Nove Reais e Noventa e
Nove Centavos

SQL> select extenso(0.02) from dual;

EXTENSO(0.02)
------------------------------------
Dois Centavos de Real

SQL> select extenso(1) from dual;

EXTENSO(1)
------------------------------------
Hum Real

SQL> select extenso(1.56) from dual;

EXTENSO(1.56)
------------------------------------
Hum Real e Cinquenta e Seis Centavos

SQL> select extenso(1001.63) from dual;

EXTENSO(1001.63)
---------------------------------------------
Hum Mil e Um Reais e Sessenta e Tres Centavos

SQL> select extenso(52987.12) from dual;

EXTENSO(52987.12)
-------------------------------------------------------------------------
Cinquenta e Dois Mil e Novecentos e Oitenta e Sete Reais  e Doze Centavos

SQL> select extenso(1000000) from dual;

EXTENSO(1000000)
-------------------
Hum Milhão de Reais

SQL> select extenso(1000000000) from dual;

EXTENSO(1000000000)

-------------------

Hum Bilhão de Reais

SQL> select extenso(1004060900) from dual;

EXTENSO(1004060900)
--------------------------------------------------------------
Hum Bilhão e Quatro Milhões e Sessenta Mil e Novecentos  Reais

SQL> select extenso(160987) from dual;

EXTENSO(160987)
--------------------------------------------------------
Cento e Sessenta Mil e Novecentos e Oitenta e Sete Reais

Função PL/SQL para Cálculo de Fórmulas Matemáticas

Certa vez, fui encarregado de criar uma função de banco de dados para um sistema na qual a mesma teria que retornar um valor resultado do cálculo matemático de outros valores ou fórmulas. Como a função ficou complexa e de uso apenas para o sistema em questão por causa de suas tabelas e campos existentes, achei melhor simplificar o seu código para mostrar aqui o seu uso.

Esta função de banco de dados FUNC_CALC_FORMULA, faz uso de uma outra função auxiliar chamada FUNC_VALIDA_FORMULA utilizada para verificar se uma fórmula matemática é válida.

É importante salientar que, nesta função de banco de dados, eu faço uso de várias funções PL/SQL, como REPLACE, INSTR, UPPER, “EXECUTE IMMEDIATE”, também faço o uso de Tabelas PL/SQL que são modeladas de forma parecida às tabelas do banco de dados e, que também, são semelhantes aos arrays (vetores) em PL/SQL do Oracle. Para exemplificar o uso desta função, seguirei os procedimentos abaixo:

Criação da Tabela que armazenará as fórmulas

SQL> CREATE TABLE TFORMULA(
  2  COD_FORMULA VARCHAR2(4),
  3  DESC_FORMULA VARCHAR2(100),
  4  SEQUENCIA NUMBER);

Tabela criada.

SQL> ALTER TABLE TFORMULA ADD CONSTRAINT PK_FORMULA PRIMARY KEY (COD_FORMULA);

Tabela alterada.

SQL> CREATE UNIQUE INDEX I_SEQUENCIA ON TFORMULA (SEQUENCIA);

índice criado.

Carga da tabela com as fórmulas

SQL> INSERT INTO TFORMULA VALUES ('V001','10 * 1.1',1);

1 linha criada.

SQL> INSERT INTO TFORMULA VALUES ('V002','20 + V001',2);

1 linha criada.

SQL> INSERT INTO TFORMULA VALUES ('V003','(V002 + V001)/2',3);

1 linha criada.

SQL> INSERT INTO TFORMULA VALUES ('V004','(V002 + V001)+ V003 * (V002 * 0.2)',4);

1 linha criada.

SQL> INSERT INTO TFORMULA VALUES ('V005','V001 + 1',5);

1 linha criada.

SQL> INSERT INTO TFORMULA VALUES ('V006','(((V004 * 1.10) + V005)/2) * 2',6);

1 linha criada.

SQL> INSERT INTO TFORMULA VALUES ('V007','V006 * 0.1',7);

1 linha criada.

SQL> INSERT INTO TFORMULA VALUES ('V008','(10 * V001) + V005 + V007',8);

1 linha criada.

SQL> INSERT INTO TFORMULA VALUES ('V009','((V001 * 1.1) * (V008 * V001)) / 5',9);

1 linha criada.

SQL> INSERT INTO TFORMULA VALUES ('V010','(V004 - V001) * 0.1',10);

1 linha criada.

SQL> INSERT INTO TFORMULA VALUES ('V011','V009 + V010',11);

1 linha criada.

SQL> INSERT INTO TFORMULA VALUES ('V012','V011 - 1',12);

1 linha criada.

SQL> INSERT INTO TFORMULA VALUES ('V013','0.1 * 100',13);

1 linha criada.

SQL> INSERT INTO TFORMULA VALUES ('V014','V002 + V013',14);

1 linha criada.

SQL> INSERT INTO TFORMULA VALUES ('V015','(V014 - V001) * 0.32',15);

1 linha criada.

SQL> INSERT INTO TFORMULA VALUES ('V016','(V009 + V015 - V002) + 10',16);

1 linha criada.

SQL> INSERT INTO TFORMULA VALUES ('V017','(V003 + (V006/2)) * V013',17);

1 linha criada.

SQL> INSERT INTO TFORMULA VALUES ('V018','(V017 + V008) / V003',18);

1 linha criada.

SQL> INSERT INTO TFORMULA VALUES ('V019','V015 * 100',19);

1 linha criada.

SQL> INSERT INTO TFORMULA VALUES ('V020','V019 + V017 + V001 + V004',20);

1 linha criada.

SQL> COMMIT;

Validação completa.

No procedimento acima eu criei uma tabela que armazenará as fórmulas, bem como a seqüência necessária para realização do cálculo:

  • COD_FORMULA: armazena o código da fórmula que poderá ser utilizado em outras fórmulas e que, necessariamente, deverá começar com a letra V. O tamanho do código da fórmula é definido pela variável N_TAMANHO da função
  • FUNC_CALC_FORMULA que no meu caso é 4 (V000 até V999).
  • DESC_FORMULA: armazena a fórmula matemática.
  • SEQUENCIA: armazena o número de seqüência que a função deverá ler ordenada de forma ascendente para calcular o valor das fórmulas.

Podemos ver abaixo que os registros das fórmulas matemáticas foram inseridas na tabela TFORMULA.

SQL> select * from tformula order by sequencia;

COD_ DESC_FORMULA                              SEQUENCIA
---- ---------------------------------------- ----------
V001 10 * 1.1                                          1
V002 20 + V001                                         2
V003 (V002 + V001)/2                                   3
V004 (V002 + V001)+ V003 * (V002 * 0.2)                4
V005 V001 + 1                                          5
V006 (((V004 * 1.10) + V005)/2) * 2                    6
V007 V006 * 0.1                                        7
V008 (10 * V001) + V005 + V007                         8
V009 ((V001 * 1.1) * (V008 * V001)) / 5                9
V010 (V004 - V001) * 0.1                              10
V011 V009 + V010                                      11
V012 V011 - 1                                         12
V013 0.1 * 100                                        13
V014 V002 + V013                                      14
V015 (V014 - V001) * 0.32                             15
V016 (V009 + V015 - V002) + 10                        16
V017 (V003 + (V006/2)) * V013                         17
V018 (V017 + V008) / V003                             18
V019 V015 * 100                                       19
V020 V019 + V017 + V001 + V004                        20

20 linhas selecionadas.

Função FUNC_CALC_FORMULA

CREATE OR REPLACE FUNCTION FUNC_CALC_FORMULA (PCOD_FORMULA VARCHAR)
RETURN NUMBER AS
  N_RETORNO NUMBER (17,2);
  N_SEQUENCIA TFORMULA.SEQUENCIA%TYPE;
  N_VALOR NUMBER;
  N_FORMULA_VALIDA NUMBER;
  N_TAMANHO NUMBER := 4;
  S_FORMULA_PRINCIPAL VARCHAR2(4000);
  S_FORMULA_SEQUENCIA VARCHAR2(4000);
  S_AUX VARCHAR2(4000);
  SQL_STMT VARCHAR2(4000);
  E_PARAMETRO_NULO EXCEPTION;
  E_FORMULA_INVALIDA EXCEPTION;
  E_FORMULA_RECURSIVA EXCEPTION;

BEGIN
  DECLARE
      TYPE REG_CALC_FORMULA IS RECORD
       (COD_FORMULA  VARCHAR2(10),
        VALOR NUMBER);

      TYPE TP_ARRAY_CALC IS TABLE OF REG_CALC_FORMULA INDEX BY BINARY_INTEGER;
      TAB_CALC TP_ARRAY_CALC;

      CURSOR FORMULA IS
        SELECT UPPER(COD_FORMULA) AS COD_FORMULA,
               UPPER(RTRIM(LTRIM(DESC_FORMULA))) AS DESC_FORMULA,
               SEQUENCIA 
        FROM TFORMULA
        WHERE SEQUENCIA <= N_SEQUENCIA
        ORDER BY SEQUENCIA;

  BEGIN
   IF RTRIM(LTRIM(PCOD_FORMULA)) IS NULL THEN 
      RAISE E_PARAMETRO_NULO;

   END IF;

   SELECT SEQUENCIA,UPPER(RTRIM(LTRIM(DESC_FORMULA))) AS DESC_FORMULA 
   INTO N_SEQUENCIA,S_FORMULA_PRINCIPAL 
   FROM TFORMULA
   WHERE UPPER(COD_FORMULA) = UPPER(PCOD_FORMULA);

   FOR REG_FORMULA IN FORMULA LOOP
      FOR I IN 1..TAB_CALC.COUNT LOOP
        S_FORMULA_PRINCIPAL := 
            REPLACE(S_FORMULA_PRINCIPAL,TAB_CALC(I).COD_FORMULA,TO_CHAR(TAB_CALC(I).VALOR));

      END LOOP;

      IF INSTR(S_FORMULA_PRINCIPAL,'V') = 0 THEN
        SQL_STMT := UPPER(REPLACE('SELECT '||S_FORMULA_PRINCIPAL||' FROM DUAL',',','.'));
        N_FORMULA_VALIDA := FUNC_VALIDA_FORMULA(S_FORMULA_PRINCIPAL);

        IF N_FORMULA_VALIDA = 0 THEN
          RAISE E_FORMULA_INVALIDA;

        END IF;

        EXECUTE IMMEDIATE REPLACE(SQL_STMT,',','.') INTO N_VALOR;

        N_RETORNO := N_VALOR;

        EXIT;     

      END IF;

      IF INSTR(REG_FORMULA.DESC_FORMULA,'V') = 0 THEN
        SQL_STMT := UPPER(REPLACE('SELECT '||REG_FORMULA.DESC_FORMULA||' FROM DUAL',',','.'));
        N_FORMULA_VALIDA := FUNC_VALIDA_FORMULA(REG_FORMULA.DESC_FORMULA);

        IF N_FORMULA_VALIDA = 0 THEN
          RAISE E_FORMULA_INVALIDA;

        END IF;

        EXECUTE IMMEDIATE REPLACE(SQL_STMT,',','.') INTO N_VALOR;

        TAB_CALC(REG_FORMULA.SEQUENCIA).COD_FORMULA  := REG_FORMULA.COD_FORMULA;
        TAB_CALC(REG_FORMULA.SEQUENCIA).VALOR        := N_VALOR;

      ELSE

        S_FORMULA_SEQUENCIA := REG_FORMULA.DESC_FORMULA;

        WHILE INSTR(S_FORMULA_SEQUENCIA,'V',1,1) > 0 LOOP
          S_AUX := SUBSTR(S_FORMULA_SEQUENCIA,INSTR(S_FORMULA_SEQUENCIA,'V',1),N_TAMANHO);

          FOR I IN 1..TAB_CALC.COUNT LOOP
            IF TAB_CALC(I).COD_FORMULA = S_AUX THEN
              N_VALOR := TAB_CALC(I).VALOR;

              EXIT;

            ELSE
              N_VALOR := NULL;

            END IF;

          END LOOP;

          IF N_VALOR IS NOT NULL THEN
            S_FORMULA_SEQUENCIA := REPLACE(S_FORMULA_SEQUENCIA,S_AUX,TO_CHAR(N_VALOR));

          ELSE
            RAISE E_FORMULA_RECURSIVA;

          END IF;

        END LOOP;

        SQL_STMT := UPPER(REPLACE('SELECT '||S_FORMULA_SEQUENCIA||' FROM DUAL',',','.'));

        N_FORMULA_VALIDA := FUNC_VALIDA_FORMULA(S_FORMULA_SEQUENCIA);

        IF N_FORMULA_VALIDA = 0 THEN
          RAISE E_FORMULA_INVALIDA;

        END IF;

        EXECUTE IMMEDIATE REPLACE(SQL_STMT,',','.') INTO N_VALOR;

        TAB_CALC(REG_FORMULA.SEQUENCIA).COD_FORMULA  := REG_FORMULA.COD_FORMULA;
        TAB_CALC(REG_FORMULA.SEQUENCIA).VALOR        := N_VALOR;

      END IF;

      N_RETORNO := N_VALOR;

   END LOOP;

   RETURN N_RETORNO;

  END FUNC_CALC_FORMULA;

EXCEPTION
     WHEN E_PARAMETRO_NULO THEN 
        RAISE_APPLICATION_ERROR (-20001,'Erro ao gerar o cálculo: Parâmetro nulo.'); 

     WHEN E_FORMULA_INVALIDA THEN 
        RAISE_APPLICATION_ERROR (-20002,'Erro ao gerar o cálculo: Fórmula inválida.'); 

     WHEN E_FORMULA_RECURSIVA THEN 
        RAISE_APPLICATION_ERROR (-20003,'Erro ao gerar o cálculo: Fórmula Recursiva.'); 

     WHEN OTHERS THEN 
        RAISE_APPLICATION_ERROR(-20004,'Erro ao gerar o cálculo: '||SQLERRM);

END;
/

Função FUNC_VALIDA_FORMULA: (Usado dentro da função principal)

CREATE OR REPLACE FUNCTION FUNC_VALIDA_FORMULA (PFORMULA VARCHAR)
RETURN NUMBER AS
  N_VALOR NUMBER;
  N_TAMANHO NUMBER := 4;
  SQL_STMT VARCHAR2(4000);
  E_PARAMETRO EXCEPTION;

BEGIN
    IF RTRIM(LTRIM(PFORMULA)) IS NULL THEN 
      RAISE E_PARAMETRO;

    END IF;

    N_VALOR := NULL;

    SQL_STMT := UPPER(REPLACE('SELECT '||PFORMULA||' FROM DUAL',',','.'));

    WHILE INSTR(SQL_STMT,'V',1,1) > 0 LOOP
      SQL_STMT := REPLACE(SQL_STMT,SUBSTR(SQL_STMT,INSTR(SQL_STMT,'V',1,1),N_TAMANHO),'1');

    END LOOP;

    EXECUTE IMMEDIATE SQL_STMT INTO N_VALOR;

    IF N_VALOR IS NOT NULL THEN 
      RETURN 1;

    ELSE
      RETURN 0;

    END IF;

EXCEPTION
     WHEN E_PARAMETRO THEN 
        RETURN 1;
     WHEN OTHERS THEN 
        RETURN 0;

END;
/

Para finalizar, segue abaixo alguns exemplos de como utilizar as funções de bancos de dados criadas neste artigo.

Exemplo 1

SQL> select cod_formula,desc_formula,func_calc_formula(cod_formula) from tformula;

COD_ DESC_FORMULA                             FUNC_CALC_FORMULA(COD_FORMULA)
---- ---------------------------------------- ------------------------------
V001 10 * 1.1                                                             11
V002 20 + V001                                                            31
V003 (V002 + V001)/2                                                      21
V004 (V002 + V001)+ V003 * (V002 * 0.2)                                172,2
V005 V001 + 1                                                             12
V006 (((V004 * 1.10) + V005)/2) * 2                                   201,42
V007 V006 * 0.1                                                        20,14
V008 (10 * V001) + V005 + V007                                        142,14
V009 ((V001 * 1.1) * (V008 * V001)) / 5                              3783,82
V010 (V004 - V001) * 0.1                                               16,12
V011 V009 + V010                                                     3799,94
V012 V011 - 1                                                        3798,94
V013 0.1 * 100                                                            10
V014 V002 + V013                                                          41
V015 (V014 - V001) * 0.32                                                9,6
V016 (V009 + V015 - V002) + 10                                       3772,42
V017 (V003 + (V006/2)) * V013                                         1217,1
V018 (V017 + V008) / V003                                              64,73
V019 V015 * 100                                                          960
V020 V019 + V017 + V001 + V004                                        2360,3

20 linhas selecionadas.

Exemplo 2

SQL> select func_calc_formula(cod_formula) valor from tformula where cod_formula = 'V020';

VALOR
------------
     2360,30

SQL> select func_calc_formula(cod_formula) valor from tformula where cod_formula = 'V012';

VALOR
------------
     3798,94

Inclusive podemos fazer uso da função “extenso” em conjunto com a função de cálculo de fórmulas como demonstrado a seguir:

SQL> select func_calc_formula(cod_formula) valor,
  2  extenso(func_calc_formula(cod_formula)) extenso
  3  from tformula;

   VALOR  EXTENSO
-------- -------------------------------------------------------------------------
      11 Onze Reais
      31 Trinta e Um Reais
      21 Vinte e Um Reais
  172,20 Cento e Setenta e Dois Reais e Vinte Centavos
      12 Doze Reais
  201,42 Duzentos e Um Reais e Quarenta e Dois Centavos
   20,14 Vinte Reais e Quatorze Centavos
  142,14 Cento e Quarenta e Dois Reais e Quatorze Centavos
 3783,82 Tres Mil e Setecentos e Oitenta e Tres Reais e Oitenta e Dois Centavos
   16,12 Dezesseis Reais e Doze Centavos
 3799,94 Tres Mil e Setecentos e Noventa e Nove Reais e Noventa e Quatro Centavos
 3798,94 Tres Mil e Setecentos e Noventa e Oito Reais e Noventa e Quatro Centavos
      10 Dez Reais
      41 Quarenta e Um Reais
    9,60 Nove Reais e Sessenta Centavos
 3772,42 Tres Mil e Setecentos e Setenta e Dois Reais e Quarenta e Dois Centavos
 1217,10 Hum Mil e Duzentos e Dezessete Reais e Dez Centavos
   64,73 Sessenta e Quatro Reais e Setenta e Tres Centavos
     960 Novecentos e Sessenta Reais
 2360,30 Dois Mil e Trezentos e Sessenta Reais e Trinta Centavos

20 linhas selecionadas.

Quão útil foi este post ?

Clique em uma estrela para classificar o post

nota média 5 / 5. Contagem de votos: 44

Sem votos ! Seja o primeiro a classificar !

Marcações:

Deixe um comentário

O seu endereço de e-mail não será publicado. Campos obrigatórios são marcados com *

plugins premium WordPress