Aprenda PL/SQL

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

Dúvidas, dicas e truques de SQL, Select, Update, Delete, cláusulas, operações com joins, Funções em SQLs, etc
  

Mensagemem Qui, 20 Dez 2007 10:08 am

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
madriano
Localização: PR

Mensagemem Qui, 20 Dez 2007 10:43 am

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;
Trevisolli
Localização: Araraquara - SP

Abraço,

Trevisolli
OCA Oracle PL/SQL Developer Certified Associate
OCP Oracle Forms Developer Certified Professional
Araraquara-SP

Mensagemem Qui, 20 Dez 2007 12:17 pm

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.

:-o
dr_gori
Localização: Porto Alegre - RS

Thomas F. G
https://www.patreon.com/glufke

Mensagemem Sex, 21 Dez 2007 7:49 am

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

Mensagemem Sex, 21 Dez 2007 8:17 am

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
madriano
Localização: PR

Mensagemem Sex, 21 Dez 2007 9:06 am

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
Tineks
Localização: Araraquara - SP

Cristiano (Tineks)
Araraquara - SP

Mensagemem Ter, 14 Nov 2017 4:02 pm

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 :-o . 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
madriano
Localização: PR

Mensagemem Qui, 16 Nov 2017 1:54 pm

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
Localização: Fortaleza - CE

att,

Daniel N.N.

Mensagemem Qui, 16 Nov 2017 2:15 pm

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.
DanielNN
Localização: Fortaleza - CE

att,

Daniel N.N.



Voltar para SQL

Quem está online

Usuários navegando neste fórum: Nenhum usuário registrado e 1 visitante