视频课程:李兴华 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;