Oracle数据库基础06-转换函数

和Java语言类似，既然SQL语言中有数据类型，那么就会存在数据类型转换。

在某些情况下，Oracle数据库需要使用某种数据类型的数据，而实际提供的却是另一种数据类型，如果Oracle能自动将提供的数据类型转换为期望的数据类型的数据，这就是隐式数据类型转换。

在Oracle中，对于直接赋值，Oracle数据库能够自动将VARCHAR2或CHAR转换到NUMBER和DATE类型，也可以将NUMBER和DATE自动转换到VARCHAR2类型。

隐式数据类型转换，不止可以在上面提到的数据类型之间进行，在另一些数据类型之间也可以进行隐式数据类型转换，比如VARCHAR2可以被隐式地转换可以被隐式地转换成ROWID。

除了隐式数据类型转换，另一种就是使用转换函数，显示的将某种数据类型转换成另外一种数据类型。

SQL语言提供了三种函数，实现从一种数据类型转换到另一种数据类型，它们分别是TO_CHAR、TO_NUMBER和TO_DATE。

1.对日期使用TO_CHAR函数

之前，所有Oracle日期值默认都以DD-MON-RR格式显示，现在用TO_CHAR函数，将日期从默认格式转换为指定格式，其语法格式如下：

TO_CHAR(date[,'fmt'])

在使用该方法时，需要注意：格式模板必须放在单引号中，并且是大小写敏感的，格式模板可以包括任意日期格式元素。

例如：

select to_char(sysdate,'yyyy/mm/dd,hh24-mi-ss') from dual;

结果：

日期格式元素

格式元素	说明	结果
CC	世纪	21
YYYYYEAR	年	2013
YYY、YY、Y	年的最后三、二、一个数字	013、13、3
Y,YYY	年，在指定位置到逗号	2,013
YEAR	拼写年	TWENTY THIRTEEN
BC、AD	根据日期自动设置为BC、AD	AD
B.C	根据日期自动设置为BC.AD	A.D
Q	季度	2
MM	月：两位数值	06
MONTH	月份的全拼，右端补齐空格，总长为九个	JUNE（右边有五个空格）
Mon	月份的前三个字母，首字母大写	Jun
RW	罗马数字月	VI
WW、W	在本年或本月的第几周	24、3
DAY	周几的全拼，右端补齐空格，总长为九个字符	MONDAY
DY	周几，前三个字母缩写	MON
DDD或DD或D	在本年、本月或本周的第几天	168、17、2（周日为第一天）

接下来我们看看fm、th和sp三个元素的使用。

fm元素的作用是删除填补的空或者前导0，例如：

执行下面语句：

select to_char(sysdate,'fmyyyy/mm/dd,hh24-mi-ss') from dual;

结果：

th和sp元素的作用是指定后缀来影响数字的显示，th加在数字后面表示序数，sp加在数字后面让数字以英文形式显示，两个元素也可以结合起来使用。

例如

 

格式元素	说明	结果
AM、PM	根据时间自动设置AM或PM	PM
A.M或P.M	根据时间自动设置A.M或P.M	P.M
HH、HH12、HH24	12小时格式（1~12）、12小时格式（1~12）、24小时格式（0~23）	03、03、15
MI	分钟（0~59）	44
SS	秒（0~59）	06
SSSSS	从午夜12点以来经过的秒数	56656

为了达到更好的输出效果，可以在格式模板中直接使用标点符号，作为各元素之间的间隔，还可以在格式模板中增加字符串，增加时需要用双引号括起来。

2.对数字使用TO_CHAR函数

当数字与字符串混用且输出是字符串时，应该使用TO_CHAR函数将数字转换为需要的字符串。

（VARCHAR2），其语法格式如下：

TO_CHAR(number[,'fmt'])

格式元素	说明	输入	举例	结果
9	数字宽度（9的个数决定显示的宽度）	2468	999999	2468（前面有两个空格）
0	显示前导0		099999	002468
$	美元符号		$999999	$2468（前面有两个空格）
L	本地货币符号		L9999	￥2468
.	指定小数点位置		999999.99	2468.00（前面有两个空格）
,	指定逗号位置		999,999	2,468（前面有两个空格）
MI	将负数右边显示负号	-2468	999999MI	2468-（前面有两个空格）
PR	将负数加上括号		9999PR	<2468>
EEEE	科学计数法		99.99999EEEE	-2.46800E+03（前面无空格）
V	乘n个10，n由V后9的个数决定		999999V99	-246800（前面有两个空格）

例如：将员工表中雇员工资以美元符号、逗号和小数点进行显示：

select ename,to_number(sal,'$999,99.99') from emp;

结果：

3.TO_NUMBER函数

在使用SQL语言的数据类型转换过程中，不可能总是向字符类型转换，有时也需要将字符串转换成数字，这时候就需要使用TO_NUMBER函数，其语法格式如下：

TO_NUMBER(char[,'fmt'])

TO_NUMBER函数示例

函数	结果
TO_NUMBER('2468','9999')	2468
TO_NUMBER('2468','999')	报错
TO_NUMBER('2468','999999')	2468
TO_NUMBER('246a','9999')	报错
TO_NUMBER(TO_CHAR(SYSDATE,'YYYY'),'9999')	2013

TO_NUMBER('2468','999')这个函数报错的原因是格式模板是'999'，要求转换后的数字在-999~999范围之间，而对'2468'使用TO_NUMBER函数，其结果是2468，超过了这个范围，所以报错。TO_NUMBER('246a','9999')这个函数报错的原因是强制对一个不能转换为数字的字符进行转换。

4.TO_DATE函数

在将字符转换为数字时，如果该字符不能转换为数字时会报错；同样的，如果将一个不能转换为日期类型的字符进行强制转换，也会报错。

TO_DATE(char[,'fmt'])

例如：

select to_date('06 03,2006','MM DD,YYYY') from dual;

结果：