原文地址：http://blog.itpub.net/29324876/viewspace-1096741/
1 Oracle 隐式转换
Oracle中对不同类型的处理具有显式类型转换(Explicit)和隐式类型转换(Implicit)两种方式，对于显式类型转换，我们是可控的，但是对于隐式类型转换，当然不建议使用,
因为很难控制，有不少缺点，但是我们很难避免碰到隐式类型转换,如果不了解隐式类型转换的规则，那么往往会改变我们SQL的执行计划，从而可能导致效率降低或其它问题。
1.1 隐式转换发生场景
1.对于INSERT和UPDATE操作，oracle会把插入值或者更新值隐式转换为字段的数据类型。
例如：
SQL> create table text(id varchar2(32),name varchar2(10),age number);
Table created.
SQL> insert into text values ('1','Jack','18');
1 row created.
SQL> update text set age='19';
1 rows updated.
SQL> select * from text;
ID NAME AGE
---------- ---------- ----------
1 Jack 19
注：insert into text values ('1','Jack','18') 相当于 insert into text values(,’1’,’Jack’,to_number('18'))
update text set age='19'相当于update text set age=to_number('19')
2.当比较字符型和数值型的值时，oracle会把字符型的值隐式转换为数值型。例如：
SQL> explain plan for select * from text where age='19';
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------
Plan hash value: 738342525
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 66 | 2 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TEXT | 2 | 66 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("AGE"=19)
注：select * from text where age='19'相当于select * from text where age=to_number('19')
SQL> explain plan for select * from text where id=1;
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------
Plan hash value: 738342525
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 38 | 2 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TEXT | 1 | 38 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(TO_NUMBER("ID")=1)
注：select * from text where id=1;相当于select * from text where to_number(id)=1
如果id列建有索引此时将失效
3.当比较字符型和日期型的数据时，oracle会把字符型转换为日期型。例如：
SQL> create table table_date(varchar_date varchar2(20),date_date Date);
Table created.
SQL> insert into table_date values(to_char(sysdate,'yyyy-mm-dd'),sysdate);
1 row created.
SQL> select * from table_date;
VARCHAR_DATE DATE_DATE
-------------------- ---------
2014-02-26 26-FEB-14
SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
Session altered.
SQL> explain plan for select * from table_date where varchar_date<sysdate;< span="" style="word-wrap: break-word;">
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------
Plan hash value: 1510990824
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 21 | 2 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TABLE_DATE | 1 | 21 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(INTERNAL_FUNCTION("VARCHAR_DATE")<sysdate@!)< span="" style="word-wrap: break-word;">
注：select * from table_date where varchar_date<sysdate< span="" style="word-wrap: break-word;">相当于
select * from table_date where to_date(varchar_date,’yyyy-mm-dd hh24:mi:ss’)<sysdate< span="" style="word-wrap: break-word;">
SQL> explain plan for select * from table_date where date_date>'2014-2-26 0:0:0';
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------
Plan hash value: 1510990824
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 21 | 2 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TABLE_DATE | 1 | 21 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("DATE_DATE">TO_DATE(' 2014-02-26 00:00:00', 'syyyy-mm-dd
hh24:mi:ss'))
注：select * from table_date where date_date>'2014-2-26 0:0:0'相当于
select * from table_date where date_date>to_date('2014-2-26 0:0:0’, ’yyyy-mm-dd hh24:mi:ss’
4. 隐式转换发正在字段列上时将使索引失效。例如：
1）当末发生隐式转换时索引有效
SQL> create table t1 as select OBJECT_ID as id ,to_char(OBJECT_ID) as vid from dba_objects;
Table created.
SQL> desc t1
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NUMBER
VID VARCHAR2(40)
SQL> create index t1_ind_vid on t1(vid);
Index created.
SQL> explain plan for select * from t1 where vid='15612';
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------
Plan hash value: 1215445203
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 35 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 35 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | T1_IND_VID | 1 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("VID"='15612')
注：未发生隐式转换正常执行索引扫
2）当字段列发生隐式转换时索引将失效
SQL> explain plan for select * from t1 where vid=15612;
Explained.
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 | | 1 | 11 | 48 (5)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T1 | 1 | 11 | 48 (5)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(TO_NUMBER("VID")=15612)
注：select * from t1 where vid=15612 相当于select * from t1 where to_number(vid)=15612,vid列发生隐式转换执行计划显示全表扫描末使用索引。
1.2 隐式转换的缺点
1. 使用显示类型转换会让我们的SQL更加容易被理解,也就是可读性更强,但是隐式类型转换却没有这个优点。
2. 隐式类型转换往往对性能产生不好的影响，特别是左值的类型被隐式转为了右值的类型。这种方式很可能使我们本来可以使用索引的而没有用上索引,也有可能会导致结
果出错。
3. 隐式类型转换可能依赖于发生转换时的上下文环境，比如1中的to_date(sysdate,fmt),一旦上下文环境改变，很可能我们的程序就不能运行。
4. 隐式类型转换的算法或规则，以后Oracle可能改变，这是很危险的，意味着旧的代码很可能在新的Oracle版本中运行出现问题(性能、错误等)，显示类型转换总是有最高
的优先级，所以显示类型转换没有这种版本更替可能带来的问题。
5. 隐式类型转换是要消耗时间的,当然同等的显式类型转换时间也差不多,最好的方法就是避免类似的转换,在显示类型转换上我们会看到，最好不要将左值进行类型转换，到
时候有索引也用不上索引，还要建函数索引，索引储存和管理开销增大。
原文地址：http://www.cnblogs.com/iliuyuet/p/4104469.html
oracle有三种最基本的数据类型，即字符型、数值型、日期型。
oracle提供的单行函数中，针对不同的数据类型，提供大量实用的函数，同时提供一系列数据类型转换函数，如下：
1）to_char
数值、日期->字符型
语法：to_char(num|date,[format mask],[nls_parameters])
参数：num|date 待转换的数值或者日期
format mask:可选参数
数字->字符型的可用格式
| 格式元素 | 元素说明 | 格式 | 数字 | 字符结果 |
| 9 | 数字宽度 | 9999 | 12 | 12 |
| 0 | 显示前面的零 | 09999 | 0012 | 00012 |
| . | 小数点的位置 | 09999.999 | 030.40 | 00030.400 |
| D | 小数点分隔符的位置(默认为句点) | 09999D999 | 030.40 | 00030.400 |
| , | 逗号的位置 | 09999,999 | 03040 | 00003,040 |
| G | 组分隔符的位置(默认为逗号) | 09999G999 | 03040 | 00003,040 |
| $ | 美元符号 | $099999 | 03040 | $003040 |
| L | 当地货币 | L099999 | 03040 | GBP003040如果nls_currency设置为GBP |
| MI | 表示负数的减号的位置 | 99999MI | -3040 | 3040- |
| PR | 包围在括号内的负数 | 99999PR | -3040 | <3040> |
| EEEE | 科学计数法 | 99.99999EEEE | 121.976 | 1.21976E+02 |
| U | Nls_dual_currency | U099999 | 03040 | CAD003040如果nls_dual_currency设置为CAD |
| V | 乘以10n次(n是V之后9的数量) | 9999V99 | 3040 | 304000 |
| S | 前面加上+或者- | S999999 | 3040 | +3040 |
日期->字符型的可用格式(示例日期:02-JUN-1975)
| 格式说明 | 说明 | 结果 |
| Y | 年的最后一位 | 5 |
| YY | 年的最后两位 | 75 |
| YYY | 年的最后三位 | 975 |
| YYYY | 四位数字表示的年 | 1975 |
| RR | 两位数字表示的年 | 75 |
| YEAR | 区别大小写并用英语拼写的年 | NINETEEN SEVENTY-FIVE |
| MM | 两位数表示的月 | 06 |
| MON | 月的三个字母缩写 | JUN |
| MONTH | 区分大小写并用英语拼写的月 | JUNE |
| D | 星期几 | 2 |
| DD | 月的两位数日(即是本月的第几天) | 02 |
| DDD | 年的日 | 153 |
| DY | 星期的三个字母缩写 | MON |
| DAY | 区分大小写并用英语拼写的星期 | MONDAY |
其他不常用的格式掩码
演示数据：24-SEP-1000 BC
| 格式元素 | 说明 | 结果 |
| W | 月的周数 | 4 |
| WW | 年的周数 | 39 |
| Q | 年的季度 | 3 |
| CC | 世纪 | 10 |
| S preceding CC,YYYY,YEAR | 如果日期是BC,那么减号就在结果之前 | -10,-1000或者-ONE THOUSAND |
| IYYY,IYY,IY,I | 分别表示4,3,2,1为ISO日期 | 1000,000,00,0 |
| BC,AD,B.C. and A.D. |
|
|
| J | 儒略日—从公元前4713年12月31日开始的天数 | 1356075 |
| IW | ISO标准周(1到53) | 39 |
| RM | 用罗马数字表示的月 | IX |
时间组件的日期格式掩码
演示数据:27-JUN-2010 21:35:13
| 格式元素 | 说明 | 结果 |
| AM,PM,A.M.和P.M. | 子午线指示器 | PM |
| HH,HH12,HH24 | 一天的小时,1-12时和0-23时 | 09,09,21 |
| MI | 分(0-59) | 35 |
| SS | 秒(0-59) | 12 |
| SSSSS | 午夜之后的秒(0-86399) | 77713 |
其他日期格式掩码
| 格式元素 | 说明和格式掩码 | 结果 |
| -/.,?#! | 标点符号:’MM.YY’ | 09.08 |
| “any character literal” | 字符值:’”Week” W “”of” Month’ | Week 2 of September |
| TH | 位置或者序数文本:’DDth”of”Month’ | 12TH of September |
| SP | 拼写出数字：’MmSP Month YYYYSP’ | Nine September Two Thousand Eight |
| THSP or SPTH | 拼写出位置或者序数:’hh24SpTh’ | Fourteenth |
2）to_date
字符值->日期值
语法：to_date (string,[format mask],[nls_parameters])
参数：string 待转换的字符值
format mask:可选参数 ，格式掩码同to_char转换为date时相同。
备注：转换时要根据给定的string设定正确的格式掩码，否则
Ora_01840:input value is not long enough for date format.
Ora_01862:the numeric value does not match the length of the format item.
3） to_number
字符值->数字值
语法：to_number (string,[format mask],[nls_parameters])
参数：string 待转换的字符值
format mask:可选参数，格式掩码同to_char转换为number时相同。
备注：如果使用较短的格式掩码就会返回错误。
例如: to_number(123.56,’999.9’)返回错误。
在oracle中，如果不同的数据类型之间关联，如果不显式转换数据，则它会根据以下规则对数据进行隐式转换
1) 对于INSERT和UPDATE操作，oracle会把插入值或者更新值隐式转换为字段的数据类型。
假如id列的数据类型为number
update t set id='1'; -> 相当于 update t set id=to_number('1');
insert into t(id) values('1') -> insert into t values(to_number('1'));
2) 对于SELECT语句，oracle会把字段的数据类型隐式转换为变量的数据类型。
如假设id列的数据类型为varchar2
select * from t where id=1; -> select * from t where to_number(id)=1;
但如果id列的数据类型为number，则
select * from t where id='1'; -> select * from t where id=to_number('1');(参考下文)
3) 当比较一个字符型和数值型的值时，oracle会把字符型的值隐式转换为数值型。
如假设id列的数据类型为number
select * from t where id='1'; -> select * from t where id=to_number('1');
4) 当比较字符型和日期型的数据时，oracle会把字符型转换为日期型。
如假设create_date为字符型，
select * from t where create_date>sysdate; -> select * from t where to_date(create_date)>sysdate;(注意，此时session的nls_date_format需要与字符串格式相符)
假设create_date为date型，
select * from t where create_date>'2006-11-11 11:11:11'; -> select * from t where create_date>to_date('2006-11-11 11:11:11'); (注意，此时session的nls_date_format需要与字符串格式相符)
5) 如果调用函数或过程等时，如果输入参数的数据类型与函数或者过程定义的参数数据类型不一直，则oracle会把输入参数的数据类型转换为函数或者过程定义的数据类型。
如假设过程如下定义 p(p_1 number)
exec p('1'); -> exec p(to_number('1')); 6)
赋值时，oracle会把等号右边的数据类型转换为左边的数据类型。
如 var a number a:='1'; - > a:=to_number('1');
6) 用连接操作符(||)时，oracle会把非字符类型的数据转换为字符类型。
select 1||'2' from dual; -> select to_char(1)||'2' from dual;
7) 如果字符类型的数据和非字符类型的数据(如number、date、rowid等)作算术运算，则oracle会将字符类型的数据转换为合适的数据类型，这些数据类型可能是number、date、rowid等。
如果CHAR/VARCHAR2 和NCHAR/NVARCHAR2之间作算术运算，
则oracle会将她们都转换为number类型的数据再做比较。
8) 比较CHAR/VARCHAR2 和NCHAR/NVARCHAR2时，如果两者字符集不一样，则默认的转换方式是将数据编码从数据库字符集转换为国家字符集。
简单总结：
比较时，一般是字符型转换为数值型，字符型转换为日期型
算术运算时，一般把字符型转换为数值型，字符型转换为日期型
连接时(||)，一般是把数值型转换为字符型,日期型转换为字符型
赋值、调用函数时，以定义的变量类型为准。
扫一扫
3282
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
