Para melhor visualização, recomendo resolução de no mínimo 1024 x 768 e navegador Mozilla Firefox


terça-feira, 10 de julho de 2007

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

Por Eduardo Legatti

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.
 

Google+

3 comentários:

Shinabe disse...

Funciona perfeitamente parabéns.

rsgrillo disse...

Eu to precisando do Número por extenso monetário, e sinceramente não entendi direito.
Não tenho tanta experiencia e me pareceu um tanto confuso, teria como me ajudar um pouco mais?
Obrigado

Eduardo Legatti disse...

Olá,

A lógica PL/SQL por dentro da função pode parecer um pouco confusa, mas não há a necessidade de entendê-la. Portanto, basta apenas criar a função EXTENSO() no schema de banco de dados desejado utilizando o código PL/SQL postado no início do artigo e usá-la como demonstrado nos exemplos.

Até mais ...

Postagens populares