问题起源于我们有一个数据表有一列数据类型为varchar2，但是其中一部分为数字的格式，需要将为数字格式的内容取出，过滤结果大于某特定值的结果。
但是实际测试发现，无论怎么调整，这个函数都会在最后执行，导致查询报错：ORA-00920：无效的数字。
准备数据
首先，可以根据我们的需求创建一个表用于以上测试，重现整个问题处理过程遇到的问题，以及最终的解决方案。
创建一个数据表有主键ID列与结果列，并添加一些数据，具体内容如下：
-- Create table
create table test_table
(
test_id varchar2(36) not null,
test_result varchar2(100)
);
-- Add comments to the columns
comment on column test_table.test_id
is '主键';
comment on column test_table.test_result
is '结果';
-- Create/Recreate primary, unique and foreign key constraints
alter table test_table
add constraint pk_test_table_test_id primary key (TEST_ID);
--测试数据
insert into test_table (TEST_ID, TEST_RESULT)
values ('25c3cb1a-063f-4b70-88af-25a5439ed79a', '10.5');
insert into test_table (TEST_ID, TEST_RESULT)
values ('5577ee74-118c-44e4-95aa-1a25424d5fb5', null);
insert into test_table (TEST_ID, TEST_RESULT)
values ('b65b288d-7760-4c4f-b8e0-58891ad2703e', '600');
insert into test_table (TEST_ID, TEST_RESULT)
values ('fff2017c-a287-411f-b0a7-6e10ed91a9dd', '阴性');
insert into test_table (TEST_ID, TEST_RESULT)
values ('a488616e-e6dc-4a7c-ad0e-7d1601f87200', '未知');
insert into test_table (TEST_ID, TEST_RESULT)
values ('076952fa-a53a-4f57-8171-2d335b75a69d', '0.01');
insert into test_table (TEST_ID, TEST_RESULT)
values ('ed8ded33-1dde-452b-a9e9-a9a14abf9618', '100');
insert into test_table (TEST_ID, TEST_RESULT)
values ('7f4ec9d4-f6f2-42c1-8711-8cfe7836d7d3', ' 7.56');
insert into test_table (TEST_ID, TEST_RESULT)
values ('9156cb89-5742-4b6f-9377-467ed5fd0de8', '19');
insert into test_table (TEST_ID, TEST_RESULT)
values ('9962b5b3-611b-4cde-a8ef-a84fba9f8e20', '213.7');
insert into test_table (TEST_ID, TEST_RESULT)
values ('bb12b750-251f-4004-9243-e9cc05790220', '1887');
insert into test_table (TEST_ID, TEST_RESULT)
values ('f3638fa6-edbe-4ab5-b231-53351c9915d1', '阴性');
insert into test_table (TEST_ID, TEST_RESULT)
values ('25aae635-95dc-4632-8682-5fef1f95bfda', '阳性');
insert into test_table (TEST_ID, TEST_RESULT)
values ('32dd89e2-4b06-4479-82a7-aa3af367c97a', '20.8');
insert into test_table (TEST_ID, TEST_RESULT)
values ('0c78b29b-17d8-4fe9-afb0-74c6dd65e627', '1625');
insert into test_table (TEST_ID, TEST_RESULT)
values ('ddc2857f-0eec-48a9-8a62-74866842ff00', '17.73');
insert into test_table (TEST_ID, TEST_RESULT)
values ('cc848ac7-9599-4139-ab4b-32cefb25d791', '54.6');
insert into test_table (TEST_ID, TEST_RESULT)
values ('498118bb-36a6-4ac0-8ee0-3c83c052625a', null);
insert into test_table (TEST_ID, TEST_RESULT)
values ('2cbb9ff1-34de-493f-9075-c3c598bdadb3', '80.4');
insert into test_table (TEST_ID, TEST_RESULT)
values ('d89832da-177d-4ee0-9a6d-25e8af276e2c', '399');
数据类型的判断
首先，从网上我们获取了找到了一个如何判断varchar2类型结果是否是数值的函数：
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;
因为最终我们在做判断，也是利用了Oracle中的TO_NUMBER函数，所以这里直接使用该函数实现数值类型的判断。
错误的查询示例
直接查询
正常来说，Oracle数据库应该会根据我们书写的条件顺序，分析语句并依次执行。其实这种写法，在旧版Oracle中没有问题，但是可能因为数据库版本的问题，实际上Oracle并不是按照我们书写的顺序执行。
例如我们执行以下SQL：
select * from test_table where to_number(test_result) > 100 and isnumeric(test_result) = 1;
select * from test_table where isnumeric(test_result) = 1 and to_number(test_result) > 100;
以上执行的提示并没有不同，均提示ORA-00920：无效的数字。
分析执行计划可以看到：
Plan Hash Value : 3979868219
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 72 | 3 | 00:00:01 |
| * 1 | TABLE ACCESS FULL | TEST_TABLE | 1 | 72 | 3 | 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
------------------------------------------
* 1 - filter(TO_NUMBER("TEST_RESULT")>100 AND "ISNUMERIC"("TEST_RESULT")=1)
无论条件顺序怎么写，均是先执行范围的判断，再执行ISNUMERIC函数，函数的执行优先级都在最后。
加一层或使用查询临时表
首先想到的方案，是否可以先将是数值结果的查询出来，然后再在这个基础上判断结果的范围。
select * from (select * from test_table where isnumeric(test_result) = 1) where to_number(test_result) > 100;
with temp as (select * from test_table where isnumeric(test_result) = 1) select * from temp where to_number(test_result) > 100;
结果仍然是提示ORA-00920：无效的数字。同样的我们分析执行计划，执行顺序仍然被“优化”为先执行范围判断：
Plan Hash Value : 3979868219
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 72 | 3 | 00:00:01 |
| * 1 | TABLE ACCESS FULL | TEST_TABLE | 1 | 72 | 3 | 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
------------------------------------------
* 1 - filter(TO_NUMBER("TEST_TABLE"."TEST_RESULT")>100 AND "ISNUMERIC"("TEST_RESULT")=1)
使用视图
首先我们可以确认的是：
select * from test_table where isnumeric(test_result) = 1;
可以顺利执行，那么我们是否可以将该部分数据设置一个视图，然后从视图中进行过滤，想到了就试一下吧，首先创建一个视图：
create or replace view v_test_table as select * from test_table where isnumeric(test_result) = 1;
然后查询视图：
select * from v_test_table where to_number(test_result) > 100;
很遗憾的是，执行查询语句，无效视图的窗体再次弹出，而执行计划没有任何变化。
Plan Hash Value : 3979868219
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 72 | 3 | 00:00:01 |
| * 1 | TABLE ACCESS FULL | TEST_TABLE | 1 | 72 | 3 | 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
------------------------------------------
* 1 - filter(TO_NUMBER("TEST_RESULT")>100 AND "ISNUMERIC"("TEST_RESULT")=1)
解决方案
其实写到这里，我已经钻进了死胡同，因为我查看了自己历史提交记录，有一个报表就是通过这种方式查询并统计数据的，所以可以确定在某个版本的Oracle中，这个方法是可行的。
但是现在的问题是，Oracle在现在测试的这个版本中，会“自作聪明”的将我们所设计的SQL语句进行“优化”，优化成Oracle理解的性能最优的状态去执行，而且我们没有办法通过设置的方法去改变这个执行顺序。
那么是否有方法能将函数的执行顺序提前呢？
同事刚好看到我处理的问题，然后提出他处理过类似的字符串类型作为数值型，进行范围的判断的报表，不过他使用的方法是case when then。分两次查询，如果字符串可以转换为数字，那么返回这个字符串本身，否则返回一个数字。
那么我们将该方案使用decode函数来实现：
select *
from (select test_id,
decode(isnumeric(test_result),
1,
to_number(test_result),
null) test_result_num
from test_table)
where test_result_num > 100;
成功执行，查看执行计划：
Plan Hash Value : 3979868219
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 72 | 3 | 00:00:01 |
| * 1 | TABLE ACCESS FULL | TEST_TABLE | 1 | 72 | 3 | 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
------------------------------------------
* 1 - filter(DECODE("ISNUMERIC"("TEST_RESULT"),1,TO_NUMBER("TEST_RESULT"),NULL)>100)
Note
-----
- dynamic sampling used for this statement
执行是将decode函数返回的结果再做比较，所以不会存在以上问题。
如果我们需要对以上数据做更新，可以这样写SQL语句：
update test_table
set test_result = '阳性'
where test_id in (select test_id
from (select test_id,
decode(isnumeric(test_result),
1,
to_number(test_result),
null) test_result_num
from test_table)
where test_result_num > 100);
