函数:
定义:函数是封装好的,可以直接拿来使用的程序
分类:
1.系统函数******数据库系统已经封装好的,可以直接拿来使用的程序
2.自定义函数 ----》pl\sql 需要程序员自己去编写,自己实现函数功能的一些程序。
1.系统函数******数据库系统已经封装好的,可以直接拿来使用的程序
(1)转换函数
(2)字符型函数
(3) 数值函数
(4) 日期函数
(5)聚合函数(分组函数)
(6)开窗函数(分析函数)
(7)其他函数
-----知识点杂乱多,重要,多记多练
1.转换函数
1.1. to_date(参数1,参数2) --将字符型数据转成日期型数据
参数1:目标字符
参数2:日期格式 yyyy/mm-dd
返回值:日期型数据
dual: oracle中一张虚拟表 --只为了补全sql语法,方便查询
select '张三' from dual;
select 100+99 from dual;
select * from dual;
select '张三' from emp;
select 100+99 from dual;
select to_date('2024','yyyy') from dual; --->2024-07-01 --格式中没有mm,返回当前月份的第一天
select to_date('202406','yyyy-mm') from dual;--->2024-06-01 ---格式中没有dd,返回参数月中的第一天
1.2. to_char(参数1,参数2)--将日期型或者数值型数据转成想要的字符型数据
参数1:目标日期或者数据型数据
参数2:格式
返回值:字符型数据
1.2.1.将数值转成字符
select 123,to_char(123),'123' from dual;
--格式:0 ,9 表示参数中的格式
0 代表格式,如果整数位无数字,则强制显示为0,若小数位无数字,则以0补全
9 代表格式,如果整数位无数字,则格式不生效, 若小数位无数字,则以0补全
FM 作用:将用0,或者9 去格式化后带来的空格 删除,还可以给用9格式化数据时的小数位补全的0 删除
select 123.45,to_char(123.45),to_char(123.45,'0000.0') from dual; ----小数位格式化,位数不够,结果小数位会四舍五入
select 123.45,to_char(123.45),to_char(123.45,'990.00'),to_char(123.45,'0000.999') from dual; --0,9格式可以混合用
select 123.4567,to_char(123.4567),to_char(123.4567,'999.99'),to_char(123.4567,'999.999') from dual;
--0作为格式,小数位精度不够
select 123.4567,to_char(123.4567),to_char(123.4567,'99.00'),to_char(123.4567,'00.000') from dual;
--无效的数字格式模型,格式化只能用0,9
select 123.4567,to_char(123.4567),to_char(123.4567,'88.00'),to_char(123.4567,'00.770') from dual;--千位符 123,456--货币符号 ¥ 人民币 $ 美元--千位符加在什么位置,就会显示在什么位置
select to_char(1234567.889,'fm99,999,999.999'),to_char(1234567.889,'fm99,9,99,9,99.999') from dual;--加货币符号时,$代表美元符号,¥:该符号不能使用,如果想显示,应该使用:L L:当地货币
select to_char(1234567.889,'fm99,999,9$99.999'), ---$ 位置不受限制,但是只能出现一次
to_char(1234567.889,'fmL99,999,L999.999') ---L 只能放在最前面
select to_char(1234567.889,'fm99,9$99,9$99.999') ---$ 位置不受限制,但是只能出现一次
1.2.2将日期转成字符
sysdate ---获取当前的系统时间
select sysdate from dual;
select sysdate,to_char(sysdate),to_char(sysdate,'yyyy-mm-dd') from dual;
select sysdate,to_char(sysdate,'yyyy-mm-dd hh:mi:ss') from dual; --->12小时制
select sysdate,to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual; ---》24小时制
select sysdate,to_char(sysdate,'yyyy-mm-dd hh12:mi:ss') from dual; --->12小时制
select sysdate,to_char(sysdate,'yyyy') from dual; ----获取年份
select sysdate,to_char(sysdate,'year') from dual; ----twenty twenty-four
select sysdate,to_char(sysdate,'mm') from dual;-----获取月份
select sysdate,to_char(sysdate,'month') from dual;-----7月
select sysdate,to_char(sysdate,'mon') from dual;-----7月
select sysdate,to_char(sysdate,'dd') from dual; ----获取当月的第几天
select sysdate,to_char(sysdate,'day') from dual; ---》星期四
select sysdate,to_char(sysdate,'d') from dual; ----获取当周的第几天 周日为第一天
select sysdate,to_char(sysdate,'ddd') from dual;----获取当年的第几天
select sysdate,to_char(sysdate,'w') from dual; ----》获取当月的第几周
select sysdate,to_char(sysdate,'ww') from dual;----》获取当年的第几周
select sysdate,to_char(sysdate,'q') from dual; ---》当年的第几季度
练习:
--找出emp表中在2月受雇的员工
select t.* ,to_char(hiredate,'mm')from emp t where to_char(hiredate,'mm')=2;
select ename, hiredate, to_char(hiredate, 'mm')
from emp
where to_char(hiredate, 'mm') = 2;
1.3. to_number(参数1,参数2)
参数1:要转换的字符串
参数2:格式
功能:将字符串参数1转为数字
返回值:数值型数据
注意:
1.被转换的字符串必须符合数据型格式。如果被转换的字符串不符合数据型格式,oracle将抛出错误提示。
2.如果数字在格式范围内的话是正确的,否则会报错
格式: 0 9
9 代表一个数字,标识一个有效位
0 代表格式时,格式中整数部分0的个数和数字位数要保持一致
$ 代表美元符号
L 代表当地货币符号
. 显示为小数点
, 显示一个千位符
2.字符型函数
2.1拼接函数
concat(字符1,字符2)
作用:将字符1和字符2 进行链接,连接成一个字符
返回值: 字符型数据
和|| 对比:
1.concat 只能链接两个参数,要想连接多个,必须要用函数的嵌套
select concat(concat(concat('姓名',ename),'的工资是'),sal )from emp;
select '姓名'||ename||'的工资是'||sal from emp;
2.concat是函数,存在于多个数据库中 , ||只适用于oracle数据库
2.2 大小写转换函数
upper(字符)
作用:将参数转为大写
返回值:字符型数据
lower(字符)
作用:将参数转为小写
返回值:字符型数据
2.3首字母大写函数
initcap(字符)
作用:将一串字符的首字符转为大写,其余转为小写
返回值:字符型数据
2.4 替换函数
(1)replace(原字符,目标字符,替换后的字符) ---整体替换
(2)translate(原字符,目标字符,替换后的字符) ---逐一对应替换
select translate('ABCDEDF','BCD','ghyttt') from dual;-->AghyEyF ---ttt多余字符不影响替换结果
select translate('abcdefgatbhifjik','abf','hmmmm') from dual;-->hmcdemghtmhimjik 逐一对应替换
select translate('abcdefgatbhifjik','abf','hm') from dual; ---》hmcdeghtmhijik -》如果参数3中没有对应的字符,则替换成 null
select translate('abcdefgatbhifjik','abf','hm ') from dual; ---》hmcde ghtmhi jik --》 f 替换成 空格
select translate('123456','#123','#') from dual; --》456 如果想筛掉参数1中某些字符,应在参数2中传入一个不相关的字符,同时,在参数3中也传入一个不相关的字符
select translate('123456','#','') from dual; --》如果替换字符(参数3)整个为空,则结果直接返回为null
select translate('123456','','h') from dual; --》如果目标字符(参数2)整个为空,则结果直接返回为null
select translate('123456','#',' ') from dual;---》123456
2.5 去除空格函数
trim(字符)
作用:去除字符左右两边的空格
返回值:字符型数据
ltrim(字符)
作用:去除字符左边的空格
返回值:字符型数据
rtrim(字符)
作用:去除字符右边的空格
返回值:字符型数据
2.6截取函数
substr(原字符,位置参数n,长度参数L)
作用:将原字符从第n位开始截取,截取出L个长度
返回值:字符型数据
例:
截取员工表中姓名的前两位字符
select ename,substr(ename,1,2) from emp;
select ename,substr(ename,0,2) from emp; --位置参数传0和1效果一样,都是从第一位开始截取
select ename,substr(ename,-1,4) from emp; --位置参数传负数,从倒数第几位开始截取,正向截取长度
select ename,substr(ename,2,-3) from emp; ---长度参数必须大于0,否则返回为空 null
select ename,substr(ename,2) from emp; ----长度参数不传参,默认截取到原字符的最后一位
select ename,substr(ename,2,10000) from emp;----长度参数超过剩余位数,截取到最后一位
2.7 填充函数
lpad(原字符,总长度L,要填充的值)
作用 :向原字符的左侧填充想要的值,使其总长达到L
返回值:字符型数据
rpad(原字符,总长度L,要填充的值)
作用 :向原字符的右侧填充想要的值,使其总长达到L
返回值:字符型数据
2.8 获取字符长度
length(参数)
作用:返回参数的字符长度
返回值:数字
lengthb(参数)
作用:返回参数的字节长度
返回值:数字
select lengthb('ertg') from dual;-----一个英文占1个字节
select lengthb('张三') from dual;---一个汉字占2个字节(GBK) 一个汉字占3个字节UTF-8)
2.9 获取字符位置的函数 定位字符位置函数
instr(参数1,参数2,n,m)
作用:定位字符位置,查询字符位置
返回值:数字
参数1:原字符
参数2:目标字符
参数3:n 从原字符的第几位开始找
参数4:m 目标字符出现的次数 不传参,默认找第1次出现
返回:目标字符出现第m次的位置(原字符的第一位开始数)
select instr('qweroracle','e',1,2),length('qweroracle') from dual;--->10
select instr('hello world',' ',1,1),length('qweroracle') from dual;--->6---空格要占一位
select instr('qwerorweracle','wer',1,2),length('qweroracle') from dual;--->7--查询字符串时,字符串要整体匹配,返回目标字符中第一个字符出现的位置
select instr('qwerorweraweclre','wer',1,3) from dual;--->0
---最后一个参数不传参,默认为第一次出现的位置
select length('jshfuakfidjdkaiwj'),instr('jshfuakfidjdkaiwj','j',3) from dual; ---》11
select instr('jshfuakfidjdkaiwj','j') from dual; --位置参数不写默认的是1
--第3个为参数是负数时,倒数第n位开始找,倒着找目标字符 位置:顺位数位置(正着数位置)
3.数值函数 number
3.1 绝对值函数
abs(数值)
作用:返回参数的绝对值
返回值:数值类型
3.2 向上取整
ceil(数值)
作用:返回离参数最近的比较大的整数
3.3 向下取整
floor(数值)
作用:返回离参数最近的比较小的整数
3.4取余函数 ---可以判断一个数的奇偶性
mod(m,n)
作用:返回m除以n的余数
返回值:数值
扩展:
--查询员工的岗位长度,判断该岗位长度的奇偶性
select job,length(job), case when mod(length(job),2)=0 then '偶数' else '奇数' end from emp;
3.5power(m,n) --取m的n次方
3.6 round(m,n)
作用:保留m中的n位小数,并且会四舍五入
返回值:数值
3.7 trunc (m,n)
作用:保留m中的n位小数,不会四舍五入
返回值:数值
3.8sign(n) --判断正负 ---可以判断两个数的大小
作用:判断正负
返回值:数值 --参数为负数,返回-1,参数为正数 返回1,参数为0 返回0,参数为空值,返回空
4.日期函数
4.1. round() --会四舍五入
trunc() --不会四舍五入
select sysdate,
round(sysdate), ---最近0点的日期
round(sysdate, 'day'), --最近的星期日
round(sysdate, 'month'),--最近的月初日期
round(sysdate, 'year'),---最近的年初日期
round(sysdate, 'q') ----最近的季初日期
from dual;
select sysdate,
trunc(sysdate), ---当天的日期
trunc(sysdate, 'day'), --本周的星期日
trunc(sysdate, 'month'),--本月的月初日期
trunc(sysdate, 'year'),---本年的年初日期
trunc(sysdate, 'q') ----本季的季初日期
from dual;
select trunc(to_date('2024-5-13','yyyy-mm-dd'),'day') from dual;
4.2 months_bewteen(date1,date2)
--传参时如果date1>date2,返回值是正数,否则,为负数
作用:计算两个日期的间隔月数
返回值:数值
4.3 add_months(date1,n) --月份加减函数
作用:在原有日期基础上加 n 个月
返回值:日期型数据
select sysdate,add_months(sysdate,6) from dual; ---月份+
select sysdate,add_months(sysdate,-6) from dual; ---月份 -
4.4 last_day(date) ---获取月末日期函数
作用:返回参数所在月的最后一天
返回值:日期型数据
日期间的直接运算:
1.日期可以直接相减
select sysdate-to_date('2024-6-26','yyyy-mm-dd') from dual; ---返回结果以天数为单位
2.天数的加减 --返回结果为日期
select sysdate+5, sysdate-5 from dual;
3.日期的加减公式
select sysdate,sysdate + interval '7' year from dual;
4.5 to_char()--获取日期的年份,月份,季度,周几
select sysdate,to_char(sysdate,'yyyy') from dual; ----获取年份
select sysdate,to_char(sysdate,'year') from dual; ----twenty twenty-four
select sysdate,to_char(sysdate,'mm') from dual;-----获取月份
select sysdate,to_char(sysdate,'month') from dual;-----7月
select sysdate,to_char(sysdate,'mon') from dual;-----7月
select sysdate,to_char(sysdate,'dd') from dual; ----获取当月的第几天
select sysdate,to_char(sysdate,'day') from dual; ---》星期四
select sysdate,to_char(sysdate,'d') from dual; ----获取当周的第几天 周日为第一天
select sysdate,to_char(sysdate,'ddd') from dual;----获取当年的第几天
select sysdate,to_char(sysdate,'w') from dual; ----》获取当月的第几周
select sysdate,to_char(sysdate,'ww') from dual;----》获取当年的第几周
select sysdate,to_char(sysdate,'q') from dual; ---》当年的第几季度
5.其他函数
5.1 nvl(参数1,参数2) --去空函数
--要求两个参数的数据类型保持一致
作用:将参数1中的空值替换成想要的值(参数2)
select ename,job, nvl(job,999) from emp; ---字符型可以兼容数字型数据
select ename,hiredate,nvl(hiredate,999) from emp; ---要求两个参数的数据类型保持一致
5.2 coalesce(参数1,参数2,参数3,参数4...)
---判断非空函数 要求参数的数据类型保持一致
返回:返回第一个不为空的参数
5.3 distinct 关键字 去重
作用:将查询结果去重
--分组查询可以实现去重
5.4 条件判断函数
5.4.1 decode
decode(条件表达式,值1,返回值1,值2,返回值2。。,默认值) --默认值如果不传参,返回空
5.4.2 case when
case when 条件1 ---和where 后边的条件写法一样 sal>3000
then 结果1 --满足条件1后 要执行的内容
when 条件2
then 结果2
。。。
else
其他结果 ---不满足以上所有条件,要执行的内容
end
练习(case when)
--1.显示员工的名字,工资,如果工资小于1000,显示小可怜,大于1000小于2000,
--显示一千多,如果工资大于2000小于3000 显示二千多,如果工资大于3000小于4000 显示三千多。。
--其他 显示 土豪
select ename,
sal,
case
when sal < 1000 then
'小可怜'
when sal >= 1000 and sal <2000 then
'一千多'
when sal >= 2000 and sal < 3000 then
'二千多'
when sal >= 3000 and sal < 4000 then
'三千多'
else
'土豪'
end
from emp;
2.某公司一三季度业绩不好,想将第一三季度入职员工降薪20%,给二四季度加薪10%
select ename ,to_char(hiredate,'q'),sal,case when to_char(hiredate,'q')=1 or to_char(hiredate,'q')=3 then sal*0.8 else sal*1.1 end from emp;
select ename,sal,decode(to_char(hiredate,'q'),1,sal*0.8,3,sal*0.8,sal*1.1) from emp;
select sal,
to_char(hiredate, 'q'),
case
when to_char(hiredate, 'q') in (1, 3) then
sal * 0.8
else
sal + sal * 0.1
end
from emp;
select ename,hiredate,sal,case when mod(to_char(hiredate,'q'),2) = 1 then sal*0.8 else sal*1.1 end from emp;
--5.emp表中工资能被3整除的,提成加工资的10%,被4整除的提成加工资的20%,被5整除的提成加工资的50%
select ename,sal,
nvl(comm, 0),
case
when mod(sal, 5) = 0 then
nvl(comm, 0) + sal * 0.5
when mod(sal, 4) = 0 then
nvl(comm, 0) + sal * 0.2
when mod(sal, 3) = 0 then
nvl(comm, 0) + sal * 0.1
else
nvl(comm, 0)
end
from emp;
select * from emp;