循序渐进学Oracle之函数（重点）

单行函数—字符函数虽然各个数据库都是支持SQL语句的，但是每一个数据库也有每一个数据库自己所支持的操作函数，这些就是单行函数，而如果要想进行数据库开发的

单行函数—字符函数

虽然各个数据库都是支持SQL语句的，但是每一个数据库也有每一个数据库自己所支持的操作函数，这些就是单行函数，而如果要想进行数据库开发的话，除了要会使用SQL之外，就是要多学习函数。

单行函数主要分为以下五类：字符函数、数字函数、日期函数、转换函数、通用函数；

1-字符函数：

字符函数的功能主要是进行字符串数据的操作，下面给出几个字符函数：

* UPPER(字符串|列):将输入的字符串变为大写返回;

* LOWER(字符串|列)：将输入的字符串变为小写返回；

* INITCAP(字符串|列)：开头首字母大写;

* LENGTH(字符串|列)：求出字符串的长度；

* REPLACE(字符串|列)：进行替换；

*SUBSTR(字符串|列,开始点[结束点])：字符串截取：

Oracle之中有一点比较麻烦，即使要验证字符串，也必须编写完整的SQL语句，所以在Oracle数据库之中为了用户的查询方便，故专门提供了一个“dual”的虚拟表。

范例1：观察转大写的函数

SELECT UPPER('hello') FROM dual ;

范例6：使用字母“_”替换姓名中所有字母“A”的信息；

col REPLACE(ename,'A','_') FORMAT A50 ; SELECT REPLACE(ename,'A','_') FROM emp ;

字符串截取操作有两种语法：

|-语法一：SUBSTPR（字符串|列，开始点），表示从开始点一直截以到结尾；

|-语法二：SUBSTPR（字符串|列，开始点，结束点），表示从开始点一直截以到结束点，截取部分内容；

范例7: 语法一：SUBSTPR（字符串|列，开始点），香港虚拟主机，表示从开始点一直截以到结尾；

SELECT ename,SUBSTR(ename,3) FROM emp ; //从第3个字符开始一直到结尾！

范例8：语法二：SUBSTPR（字符串|列，开始点，结束点），表示从开始点一直截以到结束点，截取部分内容；

SELECT ename,SUBSTR(ename,0,3) FROM emp ; //截取前3个字符！ SELECT ename,SUBSTR(ename,1,3) FROM emp ; //截取前3个字符！

范例9：要求截取每个雇员姓名的后三个字母；

* 正常思路：通过长度-2确定开始点

SELECT ename,SUBSTR(ename,LENGTH(ename)-2) FROM emp ;

* 新思路：设置负数，表示从后指定截取位置

SELECT ename,SUBSTR(ename,-3) FROM emp ;

面试题：

1、请问SUBSTR（）函数截取的时候下标是从0,还是从1开始？

* 在Oracle数据库之中，SUBSTR（）函数从0或1开始都是一样的；（见范例8）

* SUBSTR（）函数也可以设置为负数，表示由后指定截取开始点；（见范例9）

2-数字函数
数字函数一共有3个：

* ROUND(数字|列[,保留小数的位数])：四舍五入的操作；

* TRUNC(数字|列[,保留小数的位数])：舍弃指定位置的内容；

* MOD(数字1，数字2)：取模，取余数；

范例10：验证ROUND()函数

SELECT ROUND(903.5) FROM dual ;

SELECT ROUND(903.5),ROUND(-903.53567) FROM dual ; SELECT ROUND(903.5),ROUND(-903.53567),ROUND(903.53567,-1) FROM dual ; SELECT ROUND(903.5),ROUND(-903.53567),ROUND(903.53567,-1),ROUND(903.53567,2) FROM dual ;//保留2位

范例11：验证TRUNC()函数

SELECT TRUNC(903.5),TRUNC(-903.53567),TRUNC(903.53567,-1),TRUNC(903.53567,2) FROM dual ;

范例12：验证MOD()函数

SELECT MOD(10,3) FROM dual ;

以上的三个主要的数学函数，在学习Java中也会有相匹的内容！

2-日期函数

如果现在要想进行日期的操作，则首先有一个必须要解决的问题，就是如何取得当前的日期, 这个当前日期可使用“SYSDATE”取得，代码如下：

SELECT SYSDATE FROM dual ;

范例13：除了以上的当前日期之外，在日期中也可以进行若干计算：

* 日期 + 数字 = 日期，表示若干天之后的日期；

SELECT SYSDATE+3,SYSDATE+300 FROM dual ;

* 日期 - 数字 = 日期，表示若干天前的日期；

SELECT SYSDATE-3,SYSDATE-300 FROM dual ;

* 日期 – 日期 = 数字，表示的是两个日期的天数，但是肯定是大日期 – 小日期；

范例14：求出每个雇员到今天为止的雇佣天数；

SELECT ename,hiredate,SYSDATE-hiredate FROM emp ;

注：而且很多编程语言之中，也都会提出一种概念，日期可以通过数字表示出来！

除了以上三个公式之外，网站空间，也提供了以下四个操作函数：

* LAST_DAY(日期)：求出指定日期的最后一天；

范例15：求出本月的最后一天日期

SELECT LAST_DAY(SYSDATE) FROM dual ;

* NEXT_DAY(日期,星期数)：求出下一个指定星期X的日期；

范例16：求出下一个周一

SELECT NEXT_DAY(SYSDATE,'星期一') FROM dual ;

* ADD_MONTHS(日期,数字)：求出若干月之后的日期；

范例17：求出4个月后是何时

SELECT ADD_MONTHS(SYSDATE,4) FROM dual ;

* MONTHS_BETWEEN(日期1,日期2)：求出两个日期之间所经历的月份；

范例18：求出每个雇员到今天为止的雇佣月份；

SELECT ename,hiredate,MONTHS_BETWEEN(SYSDATE,hiredate) FROM emp ; SELECT ename,hiredate,TRUNC(MONTHS_BETWEEN(SYSDATE,hiredate)) FROM emp ;

在所有的开发之中，如果是日期的操作，建议使用以上函数，因为这些函数可以避免闰年的问题。

4-转换函数(核心)

现在已经接触到了Oracle数据库之中的三种数据:数字(NUMBER)、字符串(VACHAR2)、日期(DATE)，转换函数的主要功能是完成这几种数据间的相互转换的操作，一共有三种转换函数：

* TO_CHAR(字符串|列，格式字符串)：将日期或者是数字变为字符串显示；

* TO_DATE(字符串，格式字符串)：将字符串变为DATE数据显示；

* TO_NUMBER(字符串)：将字符串变为数字显示；

1、TO_CHAR()函数

在之前查询过当前的系统日期时间：

SELECT SYSDATE FROM dual ;

这个时候是按照“日-月-年”的格式显示，很明显此格式不符合于正常的思路，正常是“年-月-日”，