大家好，又见面了，我是你们的朋友全栈君。如果您正在找激活码,请点击查看最新教程,关注关注公众号 “全栈程序员社区” 获取激活教程,可能之前旧版本教程已经失效.最新Idea2022.1教程亲测有效,一键激活。

Jetbrains全系列IDE稳定放心使用

原文地址：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时，如果两者字符集不一样，则默认的转换方式是将数据编码从数据库字符集转换为国家字符集。

 

简单总结：

比较时，一般是字符型转换为数值型，字符型转换为日期型

算术运算时，一般把字符型转换为数值型，字符型转换为日期型

连接时(||)，一般是把数值型转换为字符型,日期型转换为字符型

赋值、调用函数时，以定义的变量类型为准。