而Oracle没有这个函数,所以在sql语句使用to_number函数时有时候会因为数据问题导致系统出错。
解决方案有几个,
一, 新建1个自定义函数, 利用to_number, 能转换成to_number的就返回真, 转换出错的就返回假。
CREATE OR REPLACE FUNCTION isnumeric (str IN VARCHAR2)
RETURN NUMBER
IS
v_str FLOAT;
BEGIN
IF str IS NULL
THEN
RETURN 0;
ELSE
BEGIN
SELECT TO_NUMBER (str)
INTO v_str
FROM DUAL;
EXCEPTION
WHEN INVALID_NUMBER
THEN
RETURN 0;
END;
RETURN 1;
END IF;
END isnumeric;
二,这种方法不用新增自定义系统函数,利用translate函数,只需要加在where条件里。
例如下面这个sql语句
select t.instance
from sys.v$thread t,
sys.v$parameter v
where v.name = 'thread'
and (v.value = '0' or
t.thread# = to_number(v.value))
因为高亮字句的原因,v.value中存在不能转成数字的字符数据,所以是会报错的。
而加入下面这些判断后,
select t.instance
from sys.v$thread t,
sys.v$parameter v
where v.name = 'thread'
and translate(v.value,'a1234567890.-','a') is null --必须由数字和"."和"-"组成
and translate(v.value,'1.-','1') is not null --去掉"."和"-"后至少1个字符,也就是至少有1个数字啦
and length(v.value) - length(replace(v.value,'.','')) < 2 --"."的个数必须少于2
and (instr(v.value,'-',0)= 0 or ( --没有"-"
--或者只有1个"-"字符,而且用"-"开头
(length(v.value) - length(replace(v.value,'-','')) = 1)
and substr(v.value,1,1) ='-'))
and (v.value = '0' or
t.thread# = to_number(v.value))
就不会报错了,因为在执行to_number之前已经把不符合要求的数据筛掉。
执行了上面的条件后, 其实还有类似“-.6",".1" 这样的数据保留,其实无所谓,因为to_number是可以无错执行的。
关于translate函数可以参考:http://nvd11.blog.163.com/blog/static/2000183122012112605432545/