Oracle数据库五类单行函数讲解及习题解析

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

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

一、字符函数

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

UPPER(字符串 | 列)：将输入的字符串变为大写返回; LOWER(字符串 | 列)：将输入的字符串变为小写返回; INITCAP(字符串 | 列)：开头首字母大写; LENGTH(字符串 | 列)：求出字符串的长度; REPLACE(字符串 | 列)：进行替换; SUBSTR(字符串 | 列，开始点 [,截取的个数])：字符串截取; 1、转大写的函数 SELECT UPPER('hello') FROM dual;

2、转小写的操作，将所有的雇员姓名按照小写字母返回

SELECT LOWER(ename) FROM emp;

3、将每一个雇员姓名的开头首字母大写

SELECT INITCAP(ename) FROM emp;

4、查询出每个雇员姓名的长度

SELECT ename,LENGTH(ename) FROM emp;

5、要求查询出姓名长度正好是5的雇员信息

SELECT ename,LENGTH(ename) FROM emp

WHERE LENGTH(ename)=5;

6、使用字母“_”替换掉姓名中的所有字母“A”

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

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

语法一：SUBSTR(字符串 | 列，开始点)，表示从开始点一直截取到结尾;

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

语法二：SUBSTR(字符串 | 列，开始点，结束点)，表示从开始点截取到结束点，截取部分内容;

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

7.1、要求截取每个雇员姓名的后三个字母

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

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

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

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

二、数字函数

数字函数一共有三个：

ROUND(数字 | 列 [,保留小数的位数])：四舍五入的操作; TRUNC(数字 | 列 [,保留小数的位数])：舍弃指定位置的内容; MOD(数字 1，数字2)：取模，取余数;

1、SELECT ROUND(903.53567),ROUND(-903.53567), ROUND(903.53567,2),ROUND(-90353567,-1) FROM dual;

上述查询语句的查询结果是：

904 -904 903.54 -90353570

2、

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

上述查询语句的查询结果是：

903 -903 903.53 -90353560

3、SELECT MOD(10,3) FROM dual;

上述查询语句的查询结果是：1

三、日期函数

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

SELECT SYSDATE FROM dual;

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

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

SELECT SYSDATE + 3,SYSDATE+ 300 FROM dual;

日期 – 数字 = 日期，表示若干天前的日期;

SELECT SYSDATE - 3,SYSDATE- 300 FROM dual;

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

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

SELECT ename,hiredate,SYSDATE-hiredate FROM emp;

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

除了以上的三个公式之外，也提供了如下的四个操作函数：

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

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

SELECT LAST_DAY(SYSDATE) FROM dual;

NEXT_DAY(日期，星期数)：求出下一个指定星期X的日期;

范例：求出下一个周一

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

ADD_MONTHS(日期，数字)：求出若干月之后的日期;

范例：求出四个月后的日期

SELECT ADD_MONTHS(SYSDATE,4) FROM dual;

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

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

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

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

四、转换函数

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

TO_CHAR(字符串 | 列，格式字符串)：将日期或者是数字变为字符串显示; TO_DATE(字符串，格式字符串)：将字符串变为DATE数据显示; TO_NUMBER(字符串)：将字符串变为数字显示;

a、TO_CHAR()函数

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

SELECT SYSDATE FROM dual;

这个时候是按照“日-月-年”的格式显示，很明显这种显示格式不符合正常的思路，正常是“年-月-日”，所以这种情况下可以使用TO_CHAR()函数，但是使用此函数的话需要一些格式字符串：年(yyyy)，月(mm)，日(dd)。

SELECT TO_CHAR(SYSDATE,'yyyy-mm-dd'),TO_CHAR(SYSDATE,'yyyy') year,TO_CHAR(SYSDATE,'mm') month,TO_CHAR(SYSDATE,'dd') dayFROM dual;

上述运行结果是：

2017-08-12 2017 08 12

但是这个时候的显示数据之中可以发现会存在前导0，如果要想消除掉这个0的话，可以加入一个“fm”。

SELECT TO_CHAR(SYSDATE,'fmyyyy-mm-dd') day FROM dual;

运行结果为：2017-8-12

SELECT TO_CHAR(SYSDATE,'fmyyyy-mm-dd hh24:mi:ss')day FROMdual;

上述运行结果为：2017-8-12 16:13:38

b、TO_DATE()函数

此函数的主要功能是将一个字符串变为DATE型数据。

SELECT TO_DATE('1989-09-12','yyyy-mm-dd') FROMdual;

12-9月 -89

五、通用函数

通用函数主要有两个：NVL()、DECODE()，这两个函数算是Oracle自己的特色函数了;

a、NVL()函数，处理null

b、DECODE()函数：多数值判断

DECODE()函数非常类似于程序中的if…else…语句，唯一不同的是DECODE()函数判断的是数值，而不是逻辑条件。

例如，现在要求显示全部雇员的职位，但是这些职位要求替换为中文显示：

CLERK：办事员; SALESMAN：销售; MANAGER：经理; ANALYST：分析员; PRESIDENT：总裁;

这种判断肯定是逐行进行判断，所以这个时候就必须采用DECODE(),而此函数的语法如下：

DECODE(数值 | 列 ，判断值1，显示值1，判断值2，显示值2，判断值3，显示值3，…)

范例：实现显示的操作功能

SELECT empno,ename,job,DECODE(job,'CLERK','办事员','SALESMAN','销售人员','MANAGER','经理','ANALYST','分析员','PRESIDENT','总裁') FROM emp;

DECODE()函数是整个Oracle之中最具特点的函数，一定要将其掌握。

习题讲解

1、 选择部门30中的所有员工。

SELECT * FROMemp WHERE deptno=30;

2、 列出所有办事员(CLERK)的姓名，编号和部门编号。

SELECT empno, ename, deptno FROM emp WHERE job='CLERK';

3、 找出佣金高于薪金的员工。

SELECT * FROMemp WHERE comm>sal;

4、 找出佣金高于薪金的60%的员工。

SELECT * FROMemp WHERE comm>sal*0.6;

5、 找出部门10中所有经理(MANAGER)和部门20中所有办事员(CLERK)的详细资料。

SELECT * FROM emp

WHERE (job='MANAGER' AND deptno=10) OR (job='CLERK' AND deptno=20);

6、 找出部门10中所有经理(MANAGER)，部门20中所有办事员(CLERK)，既不是经理又不是办事员但其薪金大于或等于2000的所有员工的详细资料。

SELECT * FROM emp

WHERE (job='MANAGER' AND deptno=10) OR (job='CLERK' AND deptno=20)

OR (job NOT IN ('MANAGER','CLERK') AND sal>=2000);

7、 找出收取佣金的员工的不同工作。

SELECT DISTINCT job FROMemp WHERE comm ISNOT NULL;

8、 找出不收取佣金或收取的佣金低于100的员工。

SELECT * FROMemp WHERE comm ISNULL OR comm<100;

9、 找出各月倒数第3天受雇的所有员工。

每一个雇员的雇佣日期肯定是不一样的，所以现在必须找到每一个雇员雇佣所在月的最后一天，之后按照“日期-数字”的方式求出前三天的日期，这个日期必须和雇佣日期相符合才满足条件。

SELECT * FROMemp WHERE LAST_DAY(hiredate)-2=hiredate;

10、 找出早于12年前受雇的员工。

如果要求年份，最准确的做法是使用总月数/12;

SELECT * FROMemp WHERE MONTHS_BETWEEN(SYSDATE,hiredate)/12>12;

11、 以首字母大写的方式显示所有员工的姓名。

SELECT INITCAP(ename) FROM emp;

12、 显示正好为5个字符的员工的姓名。

SELECT ename FROM emp WHERELENGTH(ename)=5;

13、 显示不带有“R”的员工的姓名。

SELECT ename FROM emp WHEREename NOT LIKE '%R%';

14、 显示所有员工姓名的前三个字符。

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

15、 显示所有员工的姓名，用“a”替换所有“A”。

SELECT REPLACE(ename,'A','a') FROM emp;

16、 显示满10年服务年限的员工的姓名和受雇日期。

SELECT ename, hiredate FROM emp

WHEREMONTHS_BETWEEN(SYSDATE,hiredate)/12>10;

17、 显示员工的详细资料，按姓名排序。

SELECT * FROMemp ORDER BYename;

18、 显示员工的姓名和受雇日期，根据其服务年限，将最老的员工排在最前面。

SELECT ename, hiredate FROM emp ORDER BY hiredate;

19、 显示所有员工的姓名、工作和薪金，按工作的降序排序，若工作相同则按薪金排序。

SELECT ename, job, sal FROM emp ORDER BY job DESC,sal;

20、 显示所有员工姓名、加入公司的年份和月份，按受雇日期所有月排序，若月份相同则将最早年份的员工排在最前面。

本程序需要从日期之中取出年份和月份，用TO_CHAR()函数完成。

SELECT ename,TO_CHAR(hiredate,'yyyy') year,TO_CHAR(hiredate,'mm') months

FROM emp

ORDER BY months,year;

21、 显示在一个月为30天的情况所有员工的日薪金，忽略余数。

SELECT ename,sal,TRUNC(sal/30) FROM emp;

22、 找出在(任何年份的)2月受聘的所有员工。

SELECT * FROMemp WHERE TO_CHAR(hiredate,'mm')=2;

23、 对于每个员工，显示其加入公司的天数。

SELECT ename,SYSDATE-hiredate FROM emp;

24、 显示姓名字段的任何位置包含“A”的所有员工的姓名。

SELECT ename FROM emp WHEREename LIKE '%A%';

25、 以年月日的方式显示所有员工的服务年限。

第一步：求出每个雇员的雇佣年数：被雇佣的总月数 / 12 = 年数;

SELECT ename, hiredate,

TRUNC(MONTHS_BETWEEN(SYSDATE,hiredate)/12) year

FROM emp;

第二步：求出月数，以上计算之中被忽略的小数点实际上都是月份，所以直接取余即可;

SELECT ename, hiredate,

TRUNC(MONTHS_BETWEEN(SYSDATE,hiredate)/12) year,

TRUNC(MOD(MONTHS_BETWEEN(SYSDATE,hiredate),12)) months

FROM emp;

第三步：求出天数，最准确的做法是在不超过30天的范围之内求;

现在已经知道当前的时间使用SYSDATE取出，而雇佣的日期使用hiredate取出，可是hiredate和SYSDATE之间的差距太大了，所以肯定会有误差，那么就必须想办法将hiredate的日期提升到与SYSDATE差距在30天的范围之内。

在之前学习过两个函数：

MONTHS_BETWEEN()：求出两个日期间的月数，如果是：MONTHS_BETWEEN(SYSDATE,hiredate)求出的是雇佣日期到今天为止的雇佣月份; ADD_MONTHS()：在一个日期上加入指定的月之后的日期，如果说hiredate + 与今天相距的月数 = 一个新的日期，而且这个新的日期肯定和SYSDATE相距不超过30天。

SELECT ename, hiredate,

TRUNC(MONTHS_BETWEEN(SYSDATE,hiredate)/12) year,

TRUNC(MOD(MONTHS_BETWEEN(SYSDATE,hiredate),12)) months,

TRUNC(SYSDATE-ADD_MONTHS(hiredate,MONTHS_BETWEEN(SYSDATE,hiredate)))day

FROM emp;

以上的这道程序，属于日期函数的综合应用。