Como identificar se um valor é numérico no "Select"

[madriano]

Olá pessoal.

Minha dúvida é teoricamente básica. Tenho uma consulta na seguinte estrutura:

SELECT codigo, nome,
NVL(ROUND(AVG(TO_NUMBER(NOTA_FINAL)),2),'-1') AS MEDIA_ALUNO
FROM nota
WHERE ...

Muito bem, o problema é especificamente em cima do campo "Nota_final". O valor onde se é armazenado é um campo em aberto, ou seja, posso colocar tanto valores númerico quanto textuais.

Preciso de uma função para poder identificar o tipo de dados retornado, a fim de utilizar junto com uma função CASE.

[]´s

[Trevisolli]

Brother,

Tenta utilizar esta idéia abaixo, acho q dá pra pegar:

Código: Selecionar todos


DECLARE
V_VALOR VARCHAR2(100);
BEGIN
   SELECT TO_NUMBER('A')
     INTO V_VALOR
    FROM DUAL;
EXCEPTION
  -- Se cair aqui, é porque é ALFANUMÉRICO, ou seja,
  -- não deixou realizar o TO_NUMBER
  WHEN OTHERS THEN
        V_VALOR := 0;   
END;


[dr_gori]

Se você quiser testar isso em várias linhas do select, creio que o ideal seja criar uma função que teste isso. Daí você pode usar tranquilamente em várias linhas sem problema e sem dar ERRO.

[madriano]

... mas ainda gostaria de poder encaixar essa verificação direto na sql da view.

[madriano]

Tentei criar uma function, mas como sou leigo nessa parte acho que fiz alguma bobagem:

Código: Selecionar todos

CREATE OR REPLACE  FUNCTION "LYCEUM"."RETORNA_NUMERICO" (
VALOR_ENVIADO character varying
)
RETURN NUMBER
   IS VALOR_RETORNADO NUMBER(3,2);
begin
   SELECT TO_NUMBER(VALOR_ENVIADO)
     INTO VALOR_RETORNADO
    FROM DUAL;
EXCEPTION
  -- Se cair aqui, é porque é ALFANUMÉRICO, ou seja,
  -- não deixou realizar o TO_NUMBER
  WHEN OTHERS THEN
        VALOR_RETORNADO := -1;
RETURN(VALOR_RETORNADO);   
end;

Mas ao fazer a chamada:

Código: Selecionar todos

SELECT RETORNA_NUMERICO('4.5') FROM DUAL
       *
ERROR at line 1:
ORA-06503: PL/SQL: Function returned without value
ORA-06512: at "LYCEUM.RETORNA_NUMERICO", line 15

[Tineks]

Opa e ai tudo beleza?

eu montei uma package com uma procedure e uma funcao.. a procedure consulta os dados e a função valida se é numero ou não. segue o código dela.

Código: Selecionar todos

CREATE OR REPLACE PACKAGE PCK_CRISTIANO IS
   FUNCTION  fun_E_NUMERO (p_VALOR VARCHAR2 DEFAULT NULL) RETURN VARCHAR2;
   PROCEDURE prc_CONSULTA;
END;

/
CREATE OR REPLACE PACKAGE BODY PCK_CRISTIANO IS
   FUNCTION fun_E_NUMERO (p_VALOR VARCHAR2 DEFAULT NULL) RETURN VARCHAR2 IS
      v_VALOR NUMBER;
   BEGIN
      v_VALOR := TO_NUMBER(p_VALOR);
      RETURN ' é número';
   EXCEPTION   
      WHEN OTHERS THEN
         RETURN ' não é número';
   END;   
   
   PROCEDURE prc_CONSULTA IS 
   BEGIN
      FOR reg IN (SELECT NOTA_FINAL,
                         fun_E_NUMERO (NOTA_FINAL) E_NUMERO
                    FROM MENS_ERRO) LOOP
         DBMS_OUTPUT.put_line(' O valor '||reg.NOTA_FINAL||' '||reg.E_NUMERO);
      END LOOP;           
   END;
END;


aqui segue o resultado da execução dela.

Código: Selecionar todos

SQL> select nome, nota_final from mens_erro;

NOME                                     NOTA_FINAL
---------------------------------------- ----------
Alberto                                  9.00
Maria                                    8.00
Zuleica                                  X
Claudia                                  7.50

SQL> BEGIN
  2     PCK_CRISTIANO.prc_consulta;
  3  END;
  4  /
O valor 9.00  é número
O valor 8.00  é número
O valor X  não é número
O valor 7.50  é número

PL/SQL procedure successfully completed.


se tiver alguma dúvida é só falar.

[]'s

[madriano]

Olá pessoal.

Eis que após alguns anos, ao fazer uma manutenção na base Oracle, me deparo novamente com a situação deste tópico. Assim sendo, por uma questão de honra, decidi resolver o problema definitivamente . Não testei a solução com package porque não se enquadraria para mim no formato de consulta que estou tentando executar. Enfim, a solução é mais simples do que pensava... corrigindo apenas a solução com "function":

Código: Selecionar todos

CREATE OR REPLACE  FUNCTION "LYCEUM"."RETORNA_NUMERICO"
    (VALOR_ENVIADO IN VARCHAR2)
RETURN NUMBER
   IS VALOR_RETORNADO NUMBER;
begin
   SELECT TO_NUMBER(VALOR_ENVIADO) INTO VALOR_RETORNADO FROM DUAL;
   -- A linha dá para simplificar ainda mais, colocando simplesmente
   -- VALOR_RETORNADO := TO_NUMBER(VALOR_ENVIADO);
   RETURN(VALOR_RETORNADO);
EXCEPTION
  WHEN OTHERS THEN                       
        RETURN(-1);
end;


Observem que declarei a variável "Valor_Retornado" somente como number, sem escala.

A versão do Oracle é a 9.2.

[]´s

[DanielNN]

Olá,
se a versão do banco fosse a partir da 10, poderia utilizar expressões regulares.
Eis uma possível solução usando regex:

Via PLSQL:

Código: Selecionar todos

DECLARE
N_NUMBER VARCHAR2(20) := '12345A';
BEGIN

  IF (regexp_like(N_NUMBER, '^[[:digit:]]+$'))THEN
    DBMS_OUTPUT.put_line('NUMBER');
  ELSE
    DBMS_OUTPUT.put_line('NOT');
  END IF; 
END;

Diretamente via SQL:

Código: Selecionar todos

SELECT *
  FROM TABLE T
WHERE REGEXP_LIKE (T.CAMPO,'^[[:digit:]]+$');

[DanielNN]

Para versão 9, é possível também usar a função TRANSLATE para excluir letras:

Código: Selecionar todos

WITH testdata AS
   (SELECT 'abcdef' txt FROM DUAL UNION ALL
    SELECT '1234567' txt FROM DUAL union all
    SELECT '0' txt FROM DUAL union all
    SELECT '123a4567x00' txt FROM DUAL union all
    SELECT '123.4567,00' txt FROM DUAL
   )
select txt,
       translate(txt,'a1234567890','a')  sem_numeros,
       translate(txt,'0abcdefghijklonopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZÁÇÉÍÓÚÀÈÌÒÙÂÊÎÔÛÃÕËÜ''á"çéíóúàèìòùâêîôûãõëü¿§¦¹²³£¢¬ªº°¨½¼¾Æ','0') so_numeros
  from testdata
where txt = translate(txt,'0abcdefghijklonopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZÁÇÉÍÓÚÀÈÌÒÙÂÊÎÔÛÃÕËÜ''á"çéíóúàèìòùâêîôûãõëü¿§¦¹²³£¢¬ªº°¨½¼¾Æ','0') /*so_numeros*/

O problema é colocar todos caracteres que você não quer considerar na lista.