视频课程：李兴华 Oracle从入门到精通视频课程
学习者：阳光罗诺
视频来源：51CTO学院
Oracle数据库从入门到精通–单行函数
在数据库中，为了方便用户的数据开发，往往会提供一系列的支持函数，利用这些函数可以针对于数据处理。
例如：在进行根据姓名查询的时候，如果说姓名本身是大写字母，而查询的是小写字母，此时就不会由任何的数据结果返回。所以针对于此类情况，往往数据保存的时候或者是查询的时候对数据进行一些处理，而这些处理每一个数据库都有自己本身的函数库，利用函数可以实现特定的功能。
在Oracle中，对于函数的基本使用结构如下：
1 返回值 函数名称（列|数据）
而根据函数的特点，单行函数可以分为以下几种：字符串函数、数值函数、日期函数、转换函数以及通用函数。
一、字符串函数
字符串函数可以针对于字符串数据进行处理，在Oracle之中对于此类函数定义有如下变化：UPPER()、LOWER()、INITCAP()、REPLACE()、LENGTH()、SUBSTR()。
1.大小写转换函数
转大写函数：字符串 UPPER(列|字符串)
转小写函数：字符串 LOWER(列|字符串)
如果要在Oracle数据库中验证字符串函数，那么就必须保证编写的是完整的SQL语句。所以为了可以方便地进行函数验证，往往会使用一张虚拟表：dual表
范例：验证函数
语法格式：
1 SELECT LOWER('SOLer He'),UPPER('SOLer He') FROM dual;
几乎所有的数据库里面都会提供这两个函数。
如果说现在要求用户自己输入一个雇员姓名，而后进行雇员信息的查找。
语法格式：
1 SELECT * FROM emp WHERE ename='&inputename';
结果如下：
用户在进行数据输入的时候几乎不会去考虑大小写，所以为了保证数据可以正常的查询出来，往往需要对输入数据进行处理。由于在数据表中所有的数据都是大写操作，那么就可以接收完输入数据之后将会自动变为大写字母。
范例：改善输入操作。
语法格式：
1 SELECT * FROM emp WHERE ename=UPPER('&inputename'）;
所以在一些要求严格的操作环境下，对于不区分大小写的操作的时候，基本上就会有两种做法：
- 在数据保存的时候将所有的数据统一变为大写或者是小写，这样子在查询的时候就可以直接利用特定的函数进行处理。
- 在数据保存的时候依然是按照原始的方式进行保存，而后在查询的时候将每一个数据中的字母变为大写形式进行处理。
在所有不区分大小写的操作的项目之中，保存数据时就必须对数据进行提前的处理。
2.首字母大写
语法格式：
1 字符串 INITCAP（列 | 数据）
范例：观察首字母大写
代码格式：
1 SELECT INITCAP ('HeLLoWorld') FROM dual;
查询输出结果：
除了首字母大写之外，其他的都是小写。
范例：将每一个雇员的姓名首字母变为大写。
代码结构：
1 SELECT INITCAP (ename) FROM emp;
查询结果：
3.计算字符串长度
语法：
1 数字 LENGTH (列 | 字符串数据)
范例：查询出每个雇员姓名以及雇员姓名的长度。
代码格式：
1 SELECT ename,LENGTH(ename) FROM emp;
查询结果：
那么所有的单行函数可以在SQL语句的任意位置上出现。
范例：查询雇员姓名长度为5的全部雇员信息。
分析：需要针对于所选的数据行进行筛选，那么就一定要在WHERE子句之中进行。
代码格式：
1 SELECT * FROM emp WHERE LENGTH(ename)=5;
结果如图：
4.字符串的替换操作
可以使用指定的内容替换原始字符串中的数据。
语法格式：
1 字符串 REPLACE (列 | 数据,要查找的内容，新的内容)
范例：将所有雇员姓名之中的字母替换为“_”。
代码示例：
1 SELECT REPLACE (ename,'A','_') FROM emp;
查询结果：
实际上可以利用REPLACE()函数可以消除字符串中的全部空格数据。
范例：消除空格数据。
代码示例：
1 SELECT REPLACE('Hello World This is my oracle',' ','') FROM dual;
查询结果：
5.字符串截取
语法一：字符串 SUBSTR(列 | 数据,开始点)，从指定的开始点一直截取到结尾
语法二：字符串 SUBSTR(列 | 数据,开始点,长度)：截取指定范围的子字符串。
范例：子字符串截取操作。
代码示例:
1 SELECT SUBSTR('helloworldnihao',11) FROM dual;
查询截图：
此种方式就是从指定位置截取到结尾。
范例：截取部分内容
代码示例：
1 SELECT SUBSTR('helloworldnihao',6,5) FROM dual;
结果：
但是对于SUBSTR()函数千万要记住一点，它的下标是从1开始的，也就是在进行截取的时候，字符串从1开始作为索引下标，但是即使设置的值是0，也是按照1来处理。
例如：代码示例：
1 SELECT SUBSTR('helloworldnihao',0,5) FROM dual;
2
3 SELECT SUBSTR('helloworldnihao',1,5) FROM dual;
结果：
范例：要求截取每一位雇员姓名的前三位字符
代码示例：
1 SELECT ename,SUBSTR(ename,1,3) FROM emp;
结果如下：
范例：要求截取姓名的后三个字母。此范例可以采用两种做法：
第一种（传统做法）：如果进行截取，那么首先一定要确认出截取的开始点，所以对于开始点，由于每一个姓名的长度都是不一样的，所以开始点也是不一样的。所以采用最好的办法就是进行计算。就可以使用LENGTH来计算长度。
代码示例：
1 SELECT ename，SUBSTR(ename,LENGTH(ename)-2) FROM emp;
查询结果：
SUBSTR()的支持，可以设置负数索引。
代码示例：
1 SELECT ename，SUBSTR(ename,-3) FROM emp;
实际上只有Oracle数据库才会支持这种负数的索引设计，其他的任何语言都是不支持。
面试题：请问Oracle中的SUBSTR()函数截取字符串的索引是从1开始还是从0开始？
答案：Oracle数据库中的字符串的索引都是从1开始的，即使设置的值是0，也会将其自动变为1开始执行。
数值函数
数值函数主要是针对于数字进行处理的，有三个主要的函数：ROUND()、TRUNC()、MOD()。
1.四舍五入操作
语法：
1 数字 ROUND（列| 数字，[保留小数位]）如果不设置小数位就表示不保留。
范例：测试四舍五入
代码示例及解析：
1 SELECT 2 3 ROUND(78915.678932654), 78916，小数点之后的内容直接进行四舍五入 4 5 ROUND(78915.678932654,2), 78915068 保留两位小数 6 7 ROUND(78915.678932654,-2), 78900，把不足5的数字全部取消了。 8 9 ROUND(78985.678932654,-2), 79000，如果超过了5则进行进位 10 11 ROUND(-15.32) -15 12 13 FROM dual;
结果如下：
2.截取小数，所有的小数都不进位。
语法：
1 数字 TRUNC（列 | 数字[,小数位]）
代码示例：
1 SELECT 2 3 TRUNC(78915.678932654), 78916 4 5 TRUNC(78915.678932654,2), 78915.68 6 7 TRUNC(78915.678932654,-2), 78900 8 9 TRUNC(78985.678932654,-2), 79000 10 11 TRUNC(-15.32) -15 12 13 FROM dual; 14 15
结果如下：
3.求模（求余数）
语法：
1 数字 MOD（列1 | 数字1，列2 | 数字2）
范例：求模操作
代码示例：
1 SELECT MOD(10,3) FROM dual;
日期函数（Oracle自己的特色）
日期处理函数主要是进行日期处理，但是整个日期处理过程中会存在一个关键词的问题。如何可以取得当前的日期时间。在Oracle中会提供一个数据伪列。指的是一个列，但是不存在于表中，可是却可以像列一样进行数据的查询。那么这个伪列就是SYSDATE。
代码示例：
1 SELECT ename, hiredate, SYSDATE FROM emp;
如果只是单纯地想要取得日期，可以使用简单一些，直接使用dual虚拟表就可以。
代码示例：
1 SELECT SYSDATE FROM dual;
如果是用具体地时间戳，那么就可以直接加SYSTIMESTAMP。
代码示例：
1 SELECT SYSDATE，SYSTIMESTAMP FROM dual;
实际上对于日期时间提供有三种计算模式：
1 日期 + 数字 = 日期（若干天后地日期） 2 3 日期 – 数字 = 日期（若干之前地日期） 4 5 日期 – 日期 = 数字 （两个日期间的天数） 6 7
测试：若干天后的日期
代码示例1：
1 SELECT SYSDATE+10 FROM dual;
代码示例2：
1 SELECT SYSDATE+10， FROM dual;
在进行日期与数字的计算之中，得到的结果都是比较容易理解的。
范例：计算每一位雇员到今天为止的雇佣天数。
代码示例：
1 SELECT ename,hiredate,SYSDATE-hiredate FROM emp;
通过以上的分析发现，如果现在只是依靠天数实际上是很难得到一个准确的年或者是月，所以为了可以精确的进行计算，在Oracle里面才提供有日期处理函数，利用这些函数可以避免掉那些闰年或者是闰月的问题。
计算两个日期间所经历的月数总和。
语法：
1 数字 MONTHS_BETWEEN(日期1，日期2)
范例：计算每一位雇员到今天为止的雇佣总月数。
代码示例：
1 SELECT ename,hiredate,MONTHS_BETWEEN(SYSDATE,hiredate) FROM emp;
实际上，现在已经存在有月的数据了，那么就表示可以准确计算年。
范例：计算每一个雇员到今天为止所雇佣的年限。
代码示例：
1 SELECT ename,hiredate,TRUNC(MONTHS_BETWEEN(SYSDATE,hiredate)/12) FROM emp;
增加若干月之后的日期。
语法：
1 日期 ADD_MONTHS(日期，月数)
范例：测试ADD_MONTHS()函数。
代码示例：
1 SELECT ADD_MONTHS(SYSDATE,4) FROM dual;
利用这种方式增加的月可以避免闰年、闰月这两个问题。
范例：计算所有还差1年满34年雇佣日期的全部雇员。
代码示例：
1 SELECT * FROM emp WHERE TRUNC(MONTHS_BETWEEN(SYSDATE, hiredate)/12)=34;
- 计算指定日期所在月的最后一天。
语法：
1 日期 LAST_DAY(日期)
范例1：计算当前日期所在月的最后一天。
代码示例：
1 SELECT LAST_DAY(SYSDATE) FROM dual;
范例2：查询出所有在雇佣所在月倒数第二天被雇佣的雇员信息
每一个雇员的雇佣日期都是不一样的，所以在每一个雇佣日期所在月的倒数第二天也不一样的。
·首先应该知道每一个雇员雇佣月的最后一天，而后利用“日期 + 数字 = 日期”，计算倒数第二天。
代码示例：
1 SELECT ename,hiredate, LAST_DAY(hiredate),LAST_DAY(hiredate)-2 2 3 FROM emp 4 5 WHERE LAST_DAY(hiredate)-2=hiredate;
- 计算下一个指定的日期
语法：日期 next_day(日期，一周时间数)
范例：计算下一个周二
代码示例：
1 SELECT NEXT_DAY(SYSDATE,'星期二') FROM dual;
【了解】综合分析：要求查询雇员的编号、姓名、雇佣日期，以及每一位雇员到今天为止所雇佣的年数、月数、天数。
假设现在的日期是：2018-07-06
现在WARD他的雇佣日期为：“1981-02-22”，所以它到今天为止的雇佣日期已经被雇佣了：35年、0月，15天。
对于该查询而言，由于日期的跨度较长。所以要想准确的计算出结果。
代码示例:
1 SELECT empno,aname,hiredate 2 3 TRUNC(MONTHS_BETWEEN(SYSDATE,hiredate)/12) year 4 5 FROM emp;
第二步：计算月
在进行计算年的时候就包含余数，余数实际上就是除以12的结果，余数就是月数。利用MOD()函数求出余数。
加上TRUNC之后的结果如下：
第三步：计算天数
计算天数的操作只有一个公式：“日期1 – 日期2 = 数字（天数）”，现在就出现了日期的问题上：
日期1：一定是当前日期，肯定是使用SYSDATE伪例。
日期：实际上可以使用MONTHS_BETWEEN()函数求出两个日期之间的月数。
观察可以发现，天数里面计算结果也会有小数点，所以我们加上TRUNC之后，就会只有整数形式的结果。
转换函数（重点）
就目前而言，在Oracle中的三种数据类型：字符串、数字、日期。所以所谓的转换函数的实现字符串与日期、数字之间的转换。
转换函数一共提供有三种：TO_CHAR()、TO_DATE()、TO_NUMBER()。针对于转换函数而言，重点的是TO_CHAR()。
1.转字符串函数，数字或者是日期可以转换为字符串。
语法：字符串 TO_CHAR（列 | 日期 | 数字，转换格式）
对于转换格式而言，主要有两类格式：
– 日期转换为字符串：年（yyyy）、月（mm）、日（hh、hh24）、分（mi）、秒（ss）。
– 数字转换为字符串：任意一位数字（9）、货币（L，本地货币）。
范例：格式化日期。
代码格式：
1 SELECT TO_CHAR(SYSDATE,'yyyy-mm-dd'),TO_CHAR(SYSDATE,'yyyy-mm-dd hh24-mi-ss')FROM dual;
在这里提供了思想：日期要想该改变日期，最终的数据类型就是字符串。如果要这样子转换实际上会破坏程序的一致性。
实际上现在可以进一步探索TO_CAHR()好处，它可以实现年月份、月、日的拆分。
范例：查询出，每个雇员的编号、姓名、雇佣年份。
代码示例：
1 SELECT * empno,ename,TO_CHAR(hiredate,'yyyy') year FROM emp;
范例：查询出所有在2月雇佣的雇员信息。
代码示例：
Oracle中实际上提供有数据类型的自动转换，如果发现比较的类型不统一，在一定的范围内是可以转换的。
TO_CHAR()函数除了可以进行日期的转换之外，也是支持数字转换的。所谓的数字转换往往是针对于数字的可读性进行一些格式化的操作。
范例：转换数字
代码示例：
1 SELECT TO_CHAR(8899,'L999,999,999,999') FROM dual;
2.转日期函数
如果说现在某一个字符按照“日 – 月 – 年”的格式去编写。那么可以自动转换为日期类型，但是也可以依靠TO_DATE()函数来完成。
语法格式：
1 TO_DATE(字符串，转换格式)
– 年（yyyy）、月（mm）、日（hh、hh24）、分（mi）、秒（ss）。
范例：实现字符串转换为日期。
代码示例：
1 SELECT TO_DATE('1995-05-02','yyyy-mm-dd') FROM dual;
3.转数字函数
可以将字符串（由字符串所组成），变为数字。
语法格式：
1 数字 TO_NUMBER(字符串)。
范例：验证转数字函数
代码示例：
1 SELECT TO_NUMBER('1') + TO_NUMBER('2') FROM dual;
通用函数（oracle自己的特色）
在Oracle中提供了两个简单的数据处理函数：NVL()、DECODE()。并且随着版本的提升，此两个函数也衍生出了许多的子函数。
1.处理null
计算出每一个雇员的年薪，包括基本工资和佣金。
代码示例：
1 SELECT empno,ename,job,(sal+comm)*12 income FROM emp;
现在发现，所有没有佣金的雇员，现在进行年收入计算的时候，最终的计算结果都是Null ，因为null在进行任何数学计算的时候，结果永远都是null。而实际上在计算之中，如果发现内容为null，如果是数字则应该使用0来替代，那么就需要利用我们的NVL()函数来解决此类问题。
语法格式：
1 NVL（列 | null，为空的默认值）如果在列上的内容不是null则使用列的数据，如果为null，则使用默认值。
代码示例：
1 SELECT empno,ename,job,sal,NVL(comm,0),(sal+NVL(comm,0))*12 income FROM emp;
2.多数值判断
所谓的多数值判断，指的是根据不同的结果可以在输出的时候进行严格数据的转换，假设每一个雇员都有自己的职位。职位现在使用的是英文描述，决定使用更换为中文描述。
代码示例：
1 SELECT empno,ename,job,DECODE(job,'CLERK','办事员','SALESMAN','销售','暂无此信息') FROM emp; 2 3 4 5 SELECT empno,ename,job,DECODE(job,'CLERK','办事员','SALESMAN','销售') FROM emp;