Oracle-函数

🍓函数

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

🍒单行行数

🍅字符函数

函数	作用
concat	连接字符串
substr	字符串截取（三个参数，源字符串，开始索引（默认从1开始），结束索引）
length	获取字符串长度
replace	字符串替换（三个参数，源字符串，需要被替换的字符串（源字符串中的），需要替换的字符串）
trim	去除字符串左右两边的空格

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

🍅数值函数

函数	作用
round	四舍五入（两个参数，源数字，保留小数位数）
trunc	全舍不入（两个参数，源数字，保留小数位数）
mod	求余数（两个参数，被除数，除数）
ceil	去除小数，直接进1，向上取整
floor	直接去除小数，向下取整

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

🍅日期函数

函数	作用
sysdate	获取当前日期
months_between	获取两个时间段中的月数（参数为日期）
add_months	获取几个月后的日期（两个参数，当前日期，月数）
round(to_number())	计算两个时间点相差的天数，时数，分数，秒数，毫秒数
trunc	获取指定日期的首

注意： 日期加上一个数还是日期，两个日期相减是一个数，天数（小数是时间，一般都是向上取整 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
	

🍅转换函数

函数名	参数	作用
to_char	(日期，日期格式)	将日期按照日期格式转换
to_char	(数字，数字格式)	将数字按照数字格式转换
to_number	(数字字符串）	将字符串转换成数字
to_date	(日期字符串，日期格式)	将字符串转换成日期

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');

🍅工具函数

函数名	参数	作用
nvl	(参数1，参数2)	如果参数1= null 则返回参数2
nvl2	（参数1，参数2，参数3）	如果参数1 = null 则返回参数3，否则返回参数2
nullif	(参数1，参数2)	如果参数1 = 参数2，那么就返回 null，否则返回参数1
coalesce	（参数1，参数2，参数3 …）	返回第一个不为null的参数

	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

🍒聚合函数

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

函数名	作用
avg	求平均值（注意： 如果求平均的列中有null值，使用这个函数求平均值是不准确的，因为它默认不会带上null的记录，所以应该使用 ceil(sum(列名) / count(1))，ceil是向上取整函数）
count	统计记录数
min	求最小值
max	求最大值
sum	求和

🍒特殊函数

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

函数名称	作用
dbms_lob.instr	判断字段中是否包含目标字段，相当于给大字段做like操作

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

🍓Oracle过滤汉字

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