oracle数据库有to_number和to_char的隐式转换。
实验如下:
SYS@192.168.80.100:1521/orcl > select * from test;
ID NAME
---------- --------------------
1 haohao
1 1
SYS@192.168.80.100:1521/orcl > desc test
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NUMBER(2)
NAME VARCHAR2(20)
打开执行计划,并执行一个有where条件的select语句如下:
SYS@192.168.80.100:1521/orcl > set autot trace exp
SYS@192.168.80.100: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语句会报错如下:
SYS@192.168.80.100:1521/orcl > select * from test where name=1;
select * from test where name=1
*
ERROR at line 1:
ORA-01722: invalid number
但是给条件的值加上单引号就不会报错
SYS@192.168.80.100:1521/orcl > select * from test where name='1';
ID NAME
---------- --------------------
1 1
原因:因为test的name列中有有数字也有字符,oracle处理where name=1的时候是对name进行to_number的隐式转换,但是name列有个值是“haohao”,由此而报错
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29117696/viewspace-1084239/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/29117696/viewspace-1084239/