Oracle-函数

🍓函数

注意： SQL函数可以没有参数但一定要有返回值

🍒单行行数

🍅字符函数

demo:

	SELECT SUBSTR('abcdefg', 0, 3) from dual; --abc
	
	SELECT SUBSTR('abcdefg',1,3) from dual; --abc
	
	SELECT SUBSTR('abcdefg', 2, 3) FROM dual;  --bcd
	
	SELECT LENGTH('abcdefg') FROM dual;  --7
	
	SELECT TRIM('   abcde fg  ')FROM dual;  --abcde fg
	
	SELECT REPLACE('hello', 'l', 'a') from dual;  --heaao
1
2
3
4
5
6
7
8
9
10
11

🍅数值函数

demo:

	select ceil(45.926) from dual; --46
	select floor(45.926) from dual; --46
	
	select round(45.926,2) from dual; --45.93
	select round(45.926,1) from dual; --45.9
	select round(45.926,0) from dual; --46
	select round(45.926,-1) from dual; --50
	select round(45.926,-2) from dual; --0
	select round(65.926,0) from dual; --100
	
	select trunc(45.926,2) from dual; --45.92
	select trunc(45.926,1) from dual; --45.9
	select trunc(45.926,0) from dual; --45
	select trunc(45.926,-1) from dual; --40
	select trunc(45.926,-2) from dual; --0
	select trunc(65.926,-2) from dual; --0

	select mod(9,3) from dual; --0
	select mod(10,3) from dual; --1
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

🍅日期函数

注意： 日期加上一个数还是日期，两个日期相减是一个数，天数（小数是时间，一般都是向上取整 ceil）

demo:

	--查询今天的日期
	select SYSDATE FROM dual;  --2019-10-04 17:07:11
	
	--查询三个月后的今天的日期
	select ADD_MONTHS(SYSDATE, 3) from dual;  --2020-01-04 17:07:57
	
	--查询3天后的现在的日期
	select SYSDATE + 3 FROM dual;  --2019-10-07 17:09:16
	
	--计算当前时间与表中时间相差的天数
	select round(to_number(sysdate - to_date(rksj,'yyyy-MM-dd HH24:mi:ss'))) from T_TEST t
	--计算当前时间与表中时间相差的小时数
	select round(to_number(sysdate - to_date(rksj,'yyyy-MM-dd HH24:mi:ss')) * 24) from T_TEST t
	--计算当前时间与表中时间相差的分钟数
	select round(to_number(sysdate - to_date(rksj,'yyyy-MM-dd HH24:mi:ss')) * 24 * 60) from T_TEST t
	--计算当前时间与表中时间相差的秒数
	select round(to_number(sysdate - to_date(rksj,'yyyy-MM-dd HH24:mi:ss')) * 24 * 60 * 60) from T_TEST t
	--计算当前时间与表中时间相差的毫秒数
	select round(to_number(sysdate - to_date(rksj,'yyyy-MM-dd HH24:mi:ss')) * 24 * 60 * 60 * 60) from T_TEST t
	
	--获取当前时间的所在天的0点0分0秒
	select trunc(sysdate) from dual
	--获取当前时间所在年的第一天0点0分0秒
	select trunc(sysdate,'yyyy') from dual
	--获取当前时间所在星期的第一天（星期日）0点0分0秒
	select trunc(sysdate,'d') from dual
	--获取当前时间所在月的第一天0点0分0秒
	select trunc(sysdate,'MM') from dual
	--获取当前时间所在小时的0分0秒
	select trunc(sysdate,'HH24') from dual
	--获取当前时间所在分钟的0秒
	select trunc(sysdate,'mi') from dual
	
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33

🍅转换函数

demo:

	--Oracle默认帮我们转了
	SELECT 100 + '10' from dual;  --110
	SELECT 100 + TO_NUMBER('10') from dual;
	
	SELECT TO_CHAR(127.73,'999.9')from dual;  --127.7
	SELECT TO_CHAR(127.73,'0000999.9')from dual;  --0000127.7
	--转16进制
	SELECT TO_CHAR(127,'xxxx')from dual;  --7f
	SELECT TO_CHAR(800,'$999.99') from dual;  --$800.00
	SELECT TO_CHAR(8000,'L9,999.99')from dual;  --$8,000.00
	
	SELECT SYSDATE FROM dual;  --2019-10-04 17:29:47
	--12小时制
	SELECT TO_CHAR(SYSDATE,'yyyy/mm/dd hh:mi:ss') from dual;  --2019/10/04 05:30:09
	--24小时制
	SELECT TO_CHAR(SYSDATE,'yyyy/mm/dd hh24:mi:ss')from dual;  --2019/10/04 17:37:13
	SELECT TO_CHAR(SYSDATE,'yyyy')from dual;  --2019
	SELECT TO_CHAR(SYSDATE,'mm')from dual;  --10
	--一个星期中的第几天，从周日开始算
	SELECT TO_CHAR(SYSDATE,'d')from dual;  --6 
	--一个月中的第几天
	SELECT TO_CHAR(SYSDATE,'dd')from dual;  --04
	--一年中的第几天
	SELECT TO_CHAR(SYSDATE,'ddd')from dual;  --277
	SELECT TO_CHAR(SYSDATE,'hh')from dual;  --05
	SELECT TO_CHAR(SYSDATE,'mi')from dual;  --30
	SELECT TO_CHAR(SYSDATE,'ss')from dual;  --09
	--星期的全写
	SELECT TO_CHAR(SYSDATE,'day')from dual; --friday  
	--星期的缩写
	SELECT TO_CHAR(SYSDATE,'dy')from dual;  --fri
	
	SELECT TO_DATE('2017-04-10', 'yyyy-mm-dd')from dual;  --2017-04-10 00:00:00
	
	--查询1981年-1985年入职的员工信息
	SELECT * from emp where hiredate between to_date('1981','yyyy') and to_date('1985','yyyy');
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36

🍅工具函数

	SELECT nvl(null,0) from dual;  --0
	SELECT nvl2(null,5,6) from dual;  --6
	SELECT nvl2(1,5,6)from dual;  --5
	SELECT nullif(5,6)from dual; --5
	SELECT nullif(6,6)from dual;  --null
	SELECT COALESCE(null,null,3,5,6) from dual;  --3
1
2
3
4
5
6

🍒聚合函数

多行函数作用于一组数据，返回一个值，多行函数会忽略空值

🍒特殊函数

dbms_lob：用于维护lob类型的大字段的函数，如常用的Blob、Clob

# 查询员工表中有爱好足球的员工
select * from emp where dmbs_lob.instr(hobby,'足球') > 0
# 查询员工表中有爱好足球的员工（从索引1开始查询，匹配到1次即可）
select * from emp where dmbs_lob.instr(hobby,'足球',1,1) > 0
1
2
3
4

🍓Oracle过滤汉字

	--lenth和lengthb对于汉字查询的长度是不一样的，我们可以按照这一特性去过滤汉字
	select length('张') from dual
	select lengthb('张') from dual
1
2
3