3.2 字符、数字、日期函数

  本节介绍单行函数中的字符函数、数字函数和日期函数。

3.2.1 字符函数

  单行字符函数接受字符数据作为输入，既可以返回字符值也可以返回数字值。作为输入的字符数据，既可以是字段名也可以是表达式。下面列举了一些常用的单行字符函数，如表3.1所示，其中输入参数用input表示，代表值为字符的字段名或表达式。

表3.1 单行字符函数

  单行字符函数和Java中的字符串函数类似，比较简单，这里不逐一使用SQL语句演示，下面通过一些简单的示例，如表3.2所示，来介绍各个函数的作用。

表3.2 单行字符函数示例

  现在需要从雇员表中获取雇员编号、姓名（包括first_name和last_name，字段标题为Name）、EMAIL、EMAIL长度、EMAIL中@符号的位置信息，且仅获取职位编号从第四个字符开始，之后的字符串是REP的雇员，其SQL语句如下：

SELECT employee_id, CONCAT(first_name || ' ', last_name) AS "Name", EMAIL,LENGTH (EMAIL), INSTR (EMAIL, '@') 
FROM employees 
WHERE SUBSTR(job_id,4)  =  'REP'

  执行SQL语句，部分显示结果如图3.3所示，因为EMAIL字段仅保存了@符号之前的内容，未保存@符号及之后的域名，所以EMAIL中@符号的位置信息结果均为0。

图3.3 单行字符函数综合使用

3.2.2 数字函数

  数字函数，接受数字输入并且返回数字值。下面列举了一些常用的单行数字函数，如表3.3所示，其中输入参数用input表示，代表值为数字的字段名或表达式。

表3.3 单行数字函数

  先看下面这个SQL语句，执行该SQL语句的结果如图3.4所示。

SELECT ROUND(123.45678),ROUND(123.45678,2),ROUND(123.45678,-1) FROM dual

图3.4 ROUND函数的使用

  这个案例中的ROUND函数很简单，这里不做解释，需要指出的是，该SQL语句中出现了一个dual表，对于这个表，需要读者有所了解。dual表的所有者是用户SYS，并且可以被所有的用户访问。它只包含一列DUMMY和带有值X的一行。dual表通常用于在没有目标表的情况下，为了达到SELECT语法完整性要求（SELECT子句必须包括FROM子句），而使用的一个不需要从表中真实取数值的表。

  下面通过一些简单的示例，如表3.4所示，介绍数字函数的使用。

表3.4 单行数字函数示例

3.2.3 日期函数

  在Oracle数据库中，以数字格式存储日期值，分别表示世纪、年、月、日、小时、分和秒。对于日期，Oracle默认显示和输入格式是DD-MON-RR，例如17-JUN-13。但在其他客户端工具中，可能会以不同的格式显示，比如在PL/SQL Dev中，显示格式为YYYY/MM/DD，例如2013/6/17。

  虽然默认的显示和输入格式是DD-MON-RR，但是存储在数据库的日期不是这种格式，所有日期和时间的组成部分都会被存储。也就是说，17-JUN-13显示的是日、月和年，但日期和时间的信息也包含在日期类型里。一个完整的日期类型如下：2013年6月17日11:22:33。

  日期函数SYSDATE，它返回当前数据库服务器的日期和时间（在SQL Server中使用GETDATE()方法）。可以执行如下的SQL语句，获取数据库服务器的日期：

SELECT SYSDATE FROM dual

  既然Oracle数据库以数字方式存储日期，也就可以用算术运算符对日期类型进行计算。在日期上加或减一个数，得到的是增加或减少该天数后的日期类型，而用两个日期类型相减，得到的是这两个日期相差的天数。

  例如想知道雇员表中，部门编号为60的雇员，截止到今天共工作了多少周，则可以使用如下的SQL语句（获得工作周的基本思路是用今天的日期减去雇佣日期，除以7后的结果就是工作周。不过此时的工作周带有小数，为了显示整数，所以在该结果的基础上，使用了ROUND函数进行了四舍五入）：

SELECT first_name || ' ' || last_name AS "Name",ROUND((SYSDATE-hire_date)/7) AS "Weeks"
FROM employees
WHERE department_id = 60

  执行该SQL语句，运行结果如图3.5所示。

图3.5 SYSDATE函数的使用

  日期函数对Oracle日期类型进行操作，下面列举了一些常用的日期函数，如表3.5所示，除了MONTHS_BETWEEN返回一个数字值，所有日期函数都返回一个日期数据类型。

表3.5 日期函数

  上面有些函数的说明不容易看懂，接下来通过一些具体的示例，如表3.6所示，会更容易明白。

表3.6 日期函数示例

  在英文系统中，月用三个字母作为缩写，1到12月分别是JAN、FEB、MAR、APR、MAY、JUN、JUL、AUG、SEP、OCT、NOV、DEC，周用具体的英文字母表示，周日、周一到周六分别是SUNDAY、MONDAY、TUESDAY、WEDNESDAY、THURSDAY、FRIDAY、SATURDAY。在中文系统（字符集）下，月和周的表示方式更简单，直接用1月、2月……12月，星期一、星期二……星期日表示。

  接下来结合雇员表看一个稍微复杂点的例子。假设在中文系统中，要查询所有受雇日期在2007年1月1日到2007年6月30日的雇员，需要查询的信息包括雇员编号、雇佣日期、已雇佣的月数、三个月试用期结束日期、提交入职资料日期（雇佣日后下一周的周一）、首月结薪日期（到受雇月最后一天）的信息，其SQL语句如下：

SELECT employee_id AS "员工编号", hire_date AS "入职日期", MONTHS_BETWEEN (SYSDATE, hire_date) AS "雇佣月数", ADD_MONTHS (hire_date, 3)  AS "试用期结束日期", NEXT_DAY (hire_date, '星期一') AS "资料提交日期", LAST_DAY(hire_date) AS "首月结薪日期"
FROM employees 
WHERE hire_date BETWEEN '1- 1月-07' AND '30- 6月-07'

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

图3.6 日期函数的使用

  ROUND和TRUNC函数用于日期时，这些函数按指定的格式化模板四舍五入或截断，默认到日，也可以四舍五入或截断到月或年。再来看一些示例，如表3.7所示。

表3.7 日期函数示例

  注意，在对月份进行四舍五入时，日期1日到15日结果在当前月的第一天，日期16日到31日结果在下月的第一天；如果对年进行四舍五入，1月到6月结果在当前年的1月1日，7月到12 月的结果在下年的1月1日。