FUNCTION IS_INTEGER( IN_VARCHAR IN VARCHAR2) RETURN INTEGER AS FLAG INTEGER; I INTEGER; BEGIN FOR I IN 1..LENGTH(IN_VARCHAR) LOOP IF ASCII(SUBSTR(IN_VARCHAR,I,1))>=48 AND ASCII(SUBSTR(IN_VARCHAR,I,1))<=57 THEN FLAG:=0; ELSE FLAG:=-1; EXIT; END IF; END LOOP; RETURN FLAG; END IS_INTEGER;
create or replace function getNumFlag return int as v_i varchar(100); v_flag int:=0; begin select to_char(sum(cc)) into v_i from tt; --cc为要验证的列名 v_flag:=1; exception when others then v_flag:=0; return v_flag; end;
--为了增加灵活性、通用性,可以把列名和表名作为变量传入 create or replace function getNumFlag(v_col_name varchar2,v_tab_name varchar2) return int as v_sql varchar2(1000); v_i varchar2(100); v_flag int:=0; begin v_sql:='select to_char(sum('||v_col_name||')) from '||v_tab_name; dbms_output.put_line('v_sql='||v_sql); execute immediate v_sql into v_i; dbms_output.put_line('v_i='||v_i); v_flag:=1; return v_flag; exception when others then v_flag:=0; return v_flag; end; /
nvl2(translate(字段名,'#1234567890.','#'),'是','否')
is NAN 非数字SQL操作符判断 where ColumnName is not NAN
谢谢各位,特别是4F的。To 5F:is not NAN Oracle中报错!WITH T0 AS ( SELECT '123A5' TEST_ID FROM DUAL UNION ALL SELECT '12345' TEST_ID FROM DUAL ) SELECT NVL2(TRANSLATE(TEST_ID,'#1234567890.','#'),'N','Y') IS_INTEGER,A.* FROM T0 A;
return int
as
v_i varchar(100);
v_flag int:=0;
begin
select to_char(sum(cc)) into v_i from tt; --cc为要验证的列名
v_flag:=1;
exception
when others then
v_flag:=0;
return v_flag;
end;
create or replace function getNumFlag(v_col_name varchar2,v_tab_name varchar2)
return int
as
v_sql varchar2(1000);
v_i varchar2(100);
v_flag int:=0;
begin
v_sql:='select to_char(sum('||v_col_name||')) from '||v_tab_name;
dbms_output.put_line('v_sql='||v_sql);
execute immediate v_sql into v_i;
dbms_output.put_line('v_i='||v_i);
v_flag:=1;
return v_flag;
exception
when others then
v_flag:=0;
return v_flag;
end;
/
where ColumnName is not NAN
SELECT '123A5' TEST_ID FROM DUAL
UNION ALL
SELECT '12345' TEST_ID FROM DUAL
)
SELECT NVL2(TRANSLATE(TEST_ID,'#1234567890.','#'),'N','Y') IS_INTEGER,A.* FROM T0 A;