oracle 类型转换函数 & oracle 隐式转换规则总结

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');

 

7) 用连接操作符(||)时，oracle会把非字符类型的数据转换为字符类型。

    select 1||'2' from dual; -> select to_char(1)||'2' from dual;

8) 如果字符类型的数据和非字符类型的数据(如number、date、rowid等)作算术运算，则oracle会将字符类型的数据转换为合适的数据类型，这些数据类型可能是number、date、rowid等。

    如果CHAR/VARCHAR2 和NCHAR/NVARCHAR2之间作算术运算，

    则oracle会将她们都转换为number类型的数据再做比较。

9) 比较CHAR/VARCHAR2 和NCHAR/NVARCHAR2时，如果两者字符集不一样，则默认的转换方式是将数据编码从数据库字符集转换为国家字符集。

 

简单总结：

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

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

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

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