ORACLE隐式类型转换 ORA-01722: 无效数字

在做开时经常会遇到ORA-01722: 无效数字 的错误,原因在于自动类型转换出错.下面举个例子:

CREATE TABLE t1(NAME VARCHAR(2));
INSERT INTO t1 VALUES('a');
INSERT INTO t1 VALUES('b');
INSERT INTO t1 VALUES('c');
COMMIT;
CREATE INDEX idx_t1_name ON t1(NAME);

----

SQL> select * from t1 where name=1;
 
select * from t1 where name=1
 
ORA-01722: 无效数字

看看执行计划:

SQL> explain plan for select * from t1 where name=1;

SQL> select * from table(dbms_xplan.display);
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     4 |    12 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1   |     4 |    12 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(TO_NUMBER("NAME")=1)
 
13 rows selected

这里做了TO_NUMBER("NAME")转换.表的读取方式为TABLE ACCESS FULL,而T1表中,没有一个字段是NUMBER类型的,所以做TO_NUMBER(NAME)就会出错.

来验证一下:

SQL> delete from t1;
 
3 rows deleted
 
SQL> commit;
 
Commit complete
 
SQL> insert into t1 values('1');
 
1 row inserted
 
SQL> commit;
 
Commit complete

SQL> select * from t1 where name=1;
 
NAME
----
1

这里就成功了,'1'可以执行to_number('1').

说明数值类型有较高的优先级,ORACLE会把字符转换成数值.

关于各个类型之间转换的优先顺序,是分多种情况的,比如insert时,会把值转换为列的类型等.详情请见官方文档

http://docs.oracle.com/cd/E11882_01/server.112/e41084/sql_elements002.htm#SQLRF00214

隐式转换的一些缺点:

1.转换错误,如上例中,尝试把表中的字符转换成数值,查询出错.

2.索引失效.上面的执行计划可以看出,用了to_number函数,这样索引就失效,可能导致很差的性能

3.某些转换可能需要上下文环境,比如date类型的显示格式.如果NLS_DATE_FORMAT改变,隐式转换可能出错

等等.

等遇到问题再补充.