3.4 转换函数

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

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

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

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

  除了隐式数据类型转换，另外一种就是使用转换函数，显式地将某种数据类型转换成另外一种数据类型，也就是本节的重点。

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

3.4.1 对日期使用TO_CHAR函数

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

TO_CHAR(date[, 'fmt'])

  在使用该方法时，需要注意：格式模板必须放在单引号中，并且是大小写敏感的，格式模板可以包括任意日期格式元素。先来看一个例子，直接认识一下如何对日期使用TO_CHAR函数，其SQL语句如下：

SELECT TO_CHAR(SYSDATE,'YYYY/MM/DD,HH24-MI-SS') FROM dual

  执行该SQL语句，结果为2013/06/17,15-44-06。在这个例子中，’YYYY/MM/DD,HH24- MI-SS’ 即格式模板，格式模板中的YYYY、MI这些称为格式元素。下面列举了日期格式的部分常用元素，如表3.8所示，其中“结果”一列是针对值为2013/06/17,15-44-06的日期类型按相应格式元素输出的结果。

表3.8 日期格式元素

续表

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

  fm元素的作用就是用来删除填补的空或者前导0，对下面两个例子进行比较，就能很快明白。

SELECT TO_CHAR(SYSDATE,'YYYY/MM/DD,HH24-MI-SS') FROM dual

  执行该SQL语句，结果为2013/06/17,15-44-06。

SELECT TO_CHAR(SYSDATE,'fmYYYY/MM/DD,HH24-MI-SS') FROM dual

  执行该SQL语句，结果为2013/6/17,15-44-6。

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

  接下来列举时间格式的部分常用元素，如表3.9所示，其中“结果”一列是针对值为2013/06/17,15-44-06的日期类型按相应格式元素输出的结果。

表3.9 时间格式元素

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

  假设现在需要显示雇员表中的雇员名字和雇佣日期，且雇佣日期以“Seventh of 6月 2013 15:44:6”格式显示，仅查询部门编号为60的雇员信息，其SQL语句如下：

SELECT first_name, TO_CHAR(hire_date, 'fmDdspth "of" Mon YYYY fmHH24:MI:SS') AS "雇佣日期" 
FROM employees
WHERE department_id = 60

  该SQL语句中还同时演示了sp和th元素的使用，执行SQL语句，显示结果如图3.7所示。

图3.7 对日期使用TO_CHAR函数

3.4.2 对数字使用TO_CHAR函数

  当数字与字符串混用且输出是字符串时，应该使用TO_CHAR函数将数字转换为需要的字符串（VARCHAR2），其语法形式如下：

TO_CHAR(number[, 'fmt'])

  表3.10列举了将数字转换为字符时，一些常用的格式元素，其中“结果”一列是针对“输入”类的数据，按格式元素输出的结果。

表3.10 数字转换为字符格式元素

  假设需要显示雇员表中的雇员名字和薪水，且薪水以“$17,000.00”格式显示，仅查询部门编号为90的雇员信息，其SQL语句如下：

SELECT first_name AS "姓名", TO_CHAR(salary, '$99,999.99') AS "薪水" 
FROM employees
WHERE department_id = 90

  执行SQL语句，显示结果如图3.8所示。

图3.8 对数字使用TO_CHAR函数

3.4.3 TO_NUMBER函数

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

TO_NUMBER(char[, 'fmt'])

  如表3.11所示，通过一些简单的示例，介绍TO_NUMBER这个函数的使用，其中最后一个示例是先将一个日期类型转换成一个字符类型，再将字符类型转换为数字类型。

表3.11 TO_NUMBER函数示例

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

3.4.4 TO_DATE函数

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

  将字符类型转换为日期类型，需要使用TO_DATE函数，这个函数的语法形式如下：

TO_DATE(char[, 'fmt'])

  来看一个不带格式模板的简单例子：

SELECT TO_DATE('17-JUN-13') FROM dual

  该SQL语句在PL/SQL Dev上执行，显示的结果是2013/6/17，表明成功转换成日期类型。

  TO_DATE函数的格式模板中可以有一个fx元素，该元素指示对需要转换的字符类型和TO_DATE函数的格式模板必须精确匹配，包括标点符号和空格。来看一个精确匹配的例子：

SELECT  TO_DATE('Jan 03, 2006', 'fxMon DD, YYYY') FROM dual

  PL/SQL Dev上执行的结果是2006/1/3，成功转换成日期类型。稍稍调整上面的SQL语句如下（其中第一行是将字符类型中天数的“03”调整为“3”，第二行是将格式模板的“YYYY”后增加了一个空格，第三行是将格式模板的“DD”和“YYYY”之后原来的逗号改成了分号）：

SELECT  TO_DATE('Jan 3, 2006', 'fxMon DD, YYYY') FROM dual
SELECT  TO_DATE('Jan 03, 2006', 'fxMon DD, YYYY ') FROM dual
SELECT  TO_DATE('Jan 03, 2006', 'fxMon DD; YYYY') FROM dual

  分别执行这三条SQL语句时都会报错，不能进行转换，可去掉fx这个元素，也就是说不需要再精确匹配。再次执行这三条SQL语句，都能正常显示出2006/1/3的结果。

  假设需要查询雇员表中雇佣日期为2006年1月3日的雇员，显示雇员的名字和雇佣日期信息，其SQL语句如下：

SELECT first_name, hire_date
FROM employees
WHERE hire_date = TO_DATE('Jan 03, 2006', 'fxMon DD, YYYY')

  执行该SQL语句，显示结果如图3.9所示。

图3.9 TO_DATE函数

  同样是上面的需求，可以换一个SQL语句的写法，执行结果一样。

SELECT first_name, hire_date
FROM employees
WHERE TO_CHAR(hire_date,'YY/MM/DD') = '06/01/03'

  比较这两个SQL语句，其中第一个在WHERE子句中是将字符类型转换为日期类型，然后将两个日期类型进行比较；而第二个SQL语句则是将日期类型转换为字符类型，将两个字符类型进行比较，其结果是一样的。