隐式转换错误:ORA-01722: invalid number

教程发布:风哥 教程分类:ITPUX技术网 更新日期:2022-02-12 浏览学习:27

oracle数据库有to_number和to_char的隐式转换。
实验如下:
mailto:SYS@192.168.80.100:1521/orcl]SYS@192.168.80.100:1521/orcl > select * from test;
ID NAME
---------- --------------------
1 haohao
1 1

mailto:SYS@192.168.80.100:1521/orcl]SYS@192.168.80.100:1521/orcl > desc test
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NUMBER(2)
NAME VARCHAR2(20)
打开执行计划,并执行一个有where条件的select语句如下:
mailto:SYS@192.168.80.100:1521/orcl]SYS@192.168.80.100:1521/orcl > set autot trace exp
[email]SYS@192.168.80.100[/email]:1521/orcl > select * from test where name=1;
Execution Plan
----------------------------------------------------------
Plan hash value: 1357081020
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 25 | 2 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TEST | 1 | 25 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(TO_NUMBER("NAME")=1)
从上面的执行计划可以看出Oracle数据库对name列进行了to_number的隐式转换,但是这条select语句会报错如下:
[email]SYS@192.168.80.100[/email]:1521/orcl > select * from test where name=1;
select * from test where name=1
*
ERROR at line 1:
ORA-01722: invalid number
但是给条件的值加上单引号就不会报错
[email]SYS@192.168.80.100[/email]:1521/orcl > select * from test where name='1';
ID NAME
---------- --------------------
1 1
原因:因为test的name列中有有数字也有字符,oracle处理where name=1的时候是对name进行to_number的隐式转换,但是name列有个值是“haohao”,由此而报错

本文标签:
网站声明:本文由风哥整理发布,转载请保留此段声明,本站所有内容将不对其使用后果做任何承诺,请读者谨慎使用!
【上一篇】
【下一篇】