以前一直没太注意过这个问题,昨天看到论坛里一个朋友发帖子在问一个相关的问题,引发了我想进一步探究这个问题的兴趣。刚才自己动手做了些实验,现在写下来看看,总结一下。
当然,这里不是探讨to_char()和to_number()函数的使用哦,呵呵。
SQL> create table t4(id varchar2(10)); --创建一个字符型类型的表
Table created.
SQL> insert into t4 values(1010);
1 row created.
SQL> insert into t4 values(01010);
1 row created.
SQL> select * from t4;
ID
----------
1010
1010
SQL> select to_char(id),id,dump(id) from t4;
TO_CHAR(ID ID DUMP(ID)
---------- ---------- ----------------------------------------
1010 1010 Typ=1 Len=4: 49,48,49,48
1010 1010 Typ=1 Len=4: 49,48,49,48
SQL> insert into t4 values('01010');
1 row created.
SQL> select to_char(id),id,dump(id) from t4;
TO_CHAR(ID ID DUMP(ID)
---------- ---------- ----------------------------------------
1010 1010 Typ=1 Len=4: 49,48,49,48
1010 1010 Typ=1 Len=4: 49,48,49,48 --这里由于在插入时没有指定引号 所以最前面的0被截断了
01010 01010 Typ=1 Len=5: 48,49,48,49,48 --可以看到 插入的时候如果加上引号把数据括起来 就会所有不同
SQL> create table t3(id number); --再创建一个数字型的表来试试
Table created.
SQL> insert into t3 values(01010);
1 row created.
SQL> insert into t3 values(1010);
1 row created.
SQL> col dump(id) format a40
SQL> select id,dump(id) from t3;
ID DUMP(ID)
---------- ----------------------------------------
1010 Typ=2 Len=3: 194,11,11
1010 Typ=2 Len=3: 194,11,11
SQL> insert into t3 values('1010');
1 row created.
SQL> insert into t3 values('01010');
1 row created.
SQL> select to_char(id),id,dump(id) from t3;
TO_CHAR(ID ID DUMP(ID)
---------- ---------- ----------------------------------------
1010 1010 Typ=2 Len=3: 194,11,11
1010 1010 Typ=2 Len=3: 194,11,11
1010 1010 Typ=2 Len=3: 194,11,11
1010 1010 Typ=2 Len=3: 194,11,11 --可以看到 typ=2了 而前面表t4中的typ=1 此时不管你插入时指不指定引号或者1010还是01010 结果都会被截断为1010 按照数字型来存储
重新创建一个表t4再来看看,在where条件中使用不同类型的值会有什么结果。
SQL> drop table t4;
Table dropped.
SQL> create table t4(id number,name varchar2(10));
Table created.
SQL> insert into t4 values(1,'1');
1 row created.
SQL> insert into t4 values('1',1);
1 row created.
SQL> explain plan for
2 select * from t4 where id=1; --此时是正常的 数字型与数字型进行比较
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2560505625
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 40 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T4 | 2 | 40 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
1 - filter("ID"=1)
Note
-----
- dynamic sampling used for this statement
17 rows selected.
SQL> explain plan for
2 select * from t4 where id='1'; --此时用数字型(id)与字符型常量('1')来进行对比 这个时候oracle会自动把字符型常量'1'转换为数字型1 所以下面执行计划和上面那句没什么不同
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2560505625
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 40 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T4 | 2 | 40 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
1 - filter("ID"=1)
Note
-----
- dynamic sampling used for this statement
17 rows selected.
SQL> explain plan for
2 select * from t4 where name=1; --这里用字符型字段name和数字型常量1来进行比较 此时oracle会使用to_number()函数将name列转换为数字型再进行对比(好像数字型比字符型厉害哦 呵呵 上面那个例子也是把字符转换为数字)
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2560505625
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 40 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T4 | 2 | 40 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
1 - filter(TO_NUMBER("NAME")=1)
Note
-----
- dynamic sampling used for this statement
17 rows selected.
SQL> explain plan for
2 select * from t4 where name='1'; --这句就没什么好说的了 字符型列name和字符型常量'1'比较
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2560505625
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 40 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T4 | 2 | 40 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
1 - filter("NAME"='1')
Note
-----
- dynamic sampling used for this statement
17 rows selected.
关于更多的ORACLE隐式类型转换可以参考另外一篇文章,里面主要讲述了number,char,varchar2三种类型的转换
http://blog.csdn.net/wh62592855/archive/2009/12/09/4969938.aspx