Oracle自定义函数解决数据类型转换出错的问题
2009-09-02 19:49 stubman 阅读(850) 评论(0) 编辑 收藏 举报
昨天开发过程(使用Oracle 9i)中,写了一个查询某字段,某个数值范围内的记录的SQL语句
如:select * from table where memeber>1 and memeber<9
但是,由于种种原因,这个字段有的记录不是数字类型(有可能是多个内容公用一个字段造成的,也有可能是输入的时候没有限制好,输入了全角字符造成的),这样这个SQL语句就会报错。
![](https://images.cnblogs.com/cnblogs_com/stubman/QQ1.png)
一开始的解决方案是:通过比较字符串来代替比较数字
如:select * from table where memeber>'1' and memeber<'9 '
但是发现,这样会有一些问题,(举例说明,在字符串的比较中,9会比101大),所以,这个语句找出来的明细中,可以找到很多比9大的数值,甚至还有其他类型的记录:
此方案宣告失败。
最后用了个自己觉得还不错的方案:
在Oracle中写一个自定义函数
Code
此函数的大概意思是:将传入的参数转换为数值类型,如果转换出现异常,则返回0;
通过这个函数,将非数值类型转换成了0,当然也可以根据需要转换成其他数值,然后这样写SQL语句:
select * from table where ToNumber(memeber)>1 and ToNumber(memeber)<9
大功告成,避免了SQL语句执行过程中报错,无法执行下去的问题,也排除了不在1,9范围之内的记录。
方法记录在此,方便记忆;
此文是搬家复制过来的。
如:select * from table where memeber>1 and memeber<9
但是,由于种种原因,这个字段有的记录不是数字类型(有可能是多个内容公用一个字段造成的,也有可能是输入的时候没有限制好,输入了全角字符造成的),这样这个SQL语句就会报错。
![](https://images.cnblogs.com/cnblogs_com/stubman/QQ1.png)
一开始的解决方案是:通过比较字符串来代替比较数字
如:select * from table where memeber>'1' and memeber<'9 '
但是发现,这样会有一些问题,(举例说明,在字符串的比较中,9会比101大),所以,这个语句找出来的明细中,可以找到很多比9大的数值,甚至还有其他类型的记录:
![](https://images.cnblogs.com/cnblogs_com/stubman/QQ2.png)
此方案宣告失败。
最后用了个自己觉得还不错的方案:
在Oracle中写一个自定义函数
![](https://www.cnblogs.com/Images/OutliningIndicators/ContractedBlock.gif)
此函数的大概意思是:将传入的参数转换为数值类型,如果转换出现异常,则返回0;
通过这个函数,将非数值类型转换成了0,当然也可以根据需要转换成其他数值,然后这样写SQL语句:
select * from table where To
大功告成,避免了SQL语句执行过程中报错,无法执行下去的问题,也排除了不在1,9范围之内的记录。
方法记录在此,方便记忆;
此文是搬家复制过来的。