to

文章目录

to_number()后比较大小出现无效数字问题分析解决前言问题原因环境描述错误语句 问题解决初步问题分析查看执行计划，分析错误原因问题解决方法一（借助临时表）：方法二 （正则替换）：方法三 （group by having）： 总结

to_number()后比较大小出现无效数字问题分析解决

前言

这是个oracle 同表查询总嵌套子查询的执行计划问题。

问题原因

环境描述

一张表 xxx (文中执行计划部分打码)， 有个yyy varchar2的字段，里边是数字，含有特殊的admin 需求，将yyy转为数字型的，去除admin，然后 过滤 yyy>30000 的结果集。

错误语句

select * from ( select to_number(yyy) as userID from xxx where xxx.ST_OBJ_ID <>'admin' ) a where a.userID >= 30000

执行后报出 无效数字的错误 （单独执行子查询是没有问题的）

问题解决

初步问题分析

出现无效数字的问题，首先确定的是 数据类型不匹配 分析上述语句 ， 就是对同一张表做的嵌套查询， 但是单独执行子查询是没有问题的。也就是说 to_number(yyy) 里边的字符是可以正常转成数字的， 又加了外层的查询后出的问题。

出现这种情况开始我一度怀疑是 to_number() 函数的问题，查看了官方文档以后，也没看出个所以然。

这其中我忽略了一个解决sql问题的很重要的步骤 就是查看 sql 执行计划 ，浪费了些许时间在网上所查结果，半天也一无所获，甚至被误导方向。

查看执行计划，分析错误原因

oracle sql develope 工具分析如下：

上述的执行计划可以看到，同一张表中做嵌套子查询的时候 oracle 把子查询与外层查询的条件放到了一块 这肯定会出问题的，因为字段里又admin等特殊的字段，执行to_number 的时候会出现问题。

为什么放在一起就出问题呢？ 之所以做了个嵌套查询就是想有个我们想要的查询条件顺序，即 第一步过滤 先将yyy字段中的不能正常转换的 “admin”过滤掉，第二部在执行 数值大小比较。 但是oacle 的执行计划中，对于同一张表的这种查询是不支持的，这就需要我们用特殊的手段。

问题解决

既然知道了问题的原因所在，那么解决问题就不难了。

着重点就是让查询条件按照整齐而的步骤执行

这里有三种解决办法

方法一（借助临时表）：

先将子查询的结果集保存在一张表中或临时表中 create table aaa as select to_char(to_number(st_obj_id)) as id from xxx where xxx.yyy <>'admin';

即aaa表中的结果是不含 admin的。

然后再查aaa表 select id from aaa where id < 30000;

即可得到最终结果。

比较笨的方法， 但是也是可以解决问题的。

方法二 （正则替换）：

select * from ( select to_number(REGEXP_REPLACE(yyy,'[^0-9]','')) userID from xxx where st_obj_type = 'USER' and xxx.yyy <>'admin' ) a where a.userID >= 30000 order by userID;

先来看下该语句的执行计划

可以看到这里的 to_number函数中 用了REGEXP_REPLACE 过滤， 非数字型的直接替换为空， 所以在这里条件执行的顺序也就无关紧要了，在to_number中的数值始终是可以转成数值型的数据，不会出现转换异常的无效数字错误。 并且 条件 xxx.yyy <>‘admin’ 也可以去掉了， 因为在函数REGEXP_REPLACE 中已经过滤掉了。

方法三 （group by having）：

就是人为的干预oracle的条件执行顺序（执行计划）

select * from ( select yyy as userID from xxx where xxx.yyy <>'admin' ) a group by a.userID having a.userID >= 30000

执行计划：

即加入了 group by … having 语句 ， 就是保证了 <> admin 这句的执行，然后再 having 执行基于上个过滤条件过滤的结果 ，再进行过滤。

最终就是保证了 查询字段 在 to_number() 函数（隐式或显示）的时候不发生转换异常。

总结

遇到这种同一张表的多个条件查询，并且各条件之间有前后顺序依赖的时候，不能简单的用 嵌套子查询的方式。

推荐用 方法二 （正则替换）， 方法三 group by 当然正则替换可能写起来稍有点复杂。 group by 方式看起来更简单一些。具体要根据实际情况去选择一个合适的方式。

总之找到问题的根结才能对症下药。