和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&#43;03&#xff08;前面无空格&#xff09; |
V | 乘n个10&#xff0c;n由V后9的个数决定 | 999999V99 | -246800&#xff08;前面有两个空格&#xff09; |
例如&#xff1a;将员工表中雇员工资以美元符号、逗号和小数点进行显示&#xff1a;
select ename,to_number(sal,&#39;$999,99.99&#39;) from emp;
结果&#xff1a;
3.TO_NUMBER函数
在使用SQL语言的数据类型转换过程中&#xff0c;不可能总是向字符类型转换&#xff0c;有时也需要将字符串转换成数字&#xff0c;这时候就需要使用TO_NUMBER函数&#xff0c;其语法格式如下&#xff1a;
TO_NUMBER(char[,&#39;fmt&#39;])
TO_NUMBER函数示例函数 | 结果 |
TO_NUMBER(&#39;2468&#39;,&#39;9999&#39;) | 2468 |
TO_NUMBER(&#39;2468&#39;,&#39;999&#39;) | 报错 |
TO_NUMBER(&#39;2468&#39;,&#39;999999&#39;) | 2468 |
TO_NUMBER(&#39;246a&#39;,&#39;9999&#39;) | 报错 |
TO_NUMBER(TO_CHAR(SYSDATE,&#39;YYYY&#39;),&#39;9999&#39;) | 2013 |
TO_NUMBER(&#39;2468&#39;,&#39;999&#39;)这个函数报错的原因是格式模板是&#39;999&#39;&#xff0c;要求转换后的数字在-999~999范围之间&#xff0c;而对&#39;2468&#39;使用TO_NUMBER函数&#xff0c;其结果是2468&#xff0c;超过了这个范围&#xff0c;所以报错。TO_NUMBER(&#39;246a&#39;,&#39;9999&#39;)这个函数报错的原因是强制对一个不能转换为数字的字符进行转换。
4.TO_DATE函数
在将字符转换为数字时&#xff0c;如果该字符不能转换为数字时会报错&#xff1b;同样的&#xff0c;如果将一个不能转换为日期类型的字符进行强制转换&#xff0c;也会报错。
TO_DATE(char[,&#39;fmt&#39;])
例如&#xff1a;
select to_date(&#39;06 03,2006&#39;,&#39;MM DD,YYYY&#39;) from dual;
结果&#xff1a;