在做开时经常会遇到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改变,隐式转换可能出错
等等.
等遇到问题再补充.