今天有同事询问ORA-01722: invalid number的错误,简单做如下测试:
RYAN@RACTSRT_1> create table tst1(id varchar2(10)); ---id字段时varchar2类型
Table created.
RYAN@RACTSRT_1> insert into tst1 values(18); ---转换为字符‘18’
1 row created.
RYAN@RACTSRT_1> select * from tst1;
ID
----------
18
RYAN@RACTSRT_1> select * from tst1 where id=18; ---where条件用的是number类型查询
ID
----------
18
RYAN@RACTSRT_1> insert into tst1 values('moon'); ---插入字符'moon'
1 row created.
RYAN@RACTSRT_1> select * from tst1 where id=18; --这里再次用number类型去查询会报错
ERROR:
ORA-01722: invalid number
no rows selected
RYAN@RACTSRT_1> select * from tst1 where id='18'; ---用字符类型去查询不会报错
ID
----------
18
RYAN@RACTSRT_1> create index ind_id_tst1 on tst1(id); ---建立一个索引
Index created.
RYAN@RACTSRT_1> set autotrace on;
ERROR:
ORA-01722: invalid number
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 2178032767
--------------------------------------------------------------------------
Id Operation Name Rows Bytes Cost (%CPU) Time
--------------------------------------------------------------------------
0 SELECT STATEMENT 1 7 3 (0) 00:00:01
* 1 TABLE ACCESS FULL TST1 1 7 3 (0) 00:00:01 --全表扫描
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(TO_NUMBER("ID")=18) -------隐式转换id列下的所有值去匹配数字类型18,因为有一个‘moon’值无法转换为数字类型所以报错
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
7 consistent gets
0 physical reads
0 redo size
521 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
RYAN@RACTSRT_1> select * from tst1 where id='18';
ID
----------
18
Execution Plan
----------------------------------------------------------
Plan hash value: 3055109003
--------------------------------------------------------------------------------
Id Operation Name Rows Bytes Cost (%CPU) Time
--------------------------------------------------------------------------------
0 SELECT STATEMENT 1 4 1 (0) 00:00:01
* 1 INDEX RANGE SCAN IND_ID_TST1 1 4 1 (0) 00:00:01 ---注意这里走索引扫
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("ID"='18') ---不再有隐式转换
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
520 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed