5.7总结：函数

函数

SQL 函数会对传递进来的参数进行处理，并返回一个处理结果，也就是返回一个值。通常用于返回特定的数据，可以很方便地转换和处理数据，Oracle 数据库中主要使用两种类型的函数：

• 单行函数：对每一个函数应用在表的记录中时，只能输入一行结果，返回一个结果，有：

  字符函数：对字符串操作。
  数字函数：对数字进行计算，返回一个数字。
  转换函数：可以将一种数据类型转换为另外一种数据类型。
  日期函数：对日期和时间进行处理。

• 聚合函数：聚合函数同时可以对多行数据进行操作，并返回一个结果。

自定义函数

--加法计算器 输入两个数 返回一个和 
create function getSum(n1 in number,n2 in number) return number 
as he number(4); 
begin he:=n1+n2; return he; end; 
select getSum(3,5) from dual 102 
select empno,deptno,sal,comm,getSum(empno,deptno) from emp

字符函数

length 计算字符串长度

length 不区分中英文
lengthb 区分中英文

upper 和 lower 大/小写转换

concat 连接字符串

只能连接两个字符

lpad 和 rpad 左/右填充

-- 对原始字符串str向左填充指定字符（str_p，可不填，默认空格），直到字符串达到n个
lpad(str,len,str_p)
-- 对ABC向左填充*直到变为5个字符，结果为'**ABC'
select lpad('ABC',5,'*') from dual
-- str_p不填时默认填充空格，结果为'  ABC'
select lpad('ABC',5) from dual
-- 若len小于原始字符，则改为保留左侧len个字符
select lpad('ABC',2) from dual -- 结果为AB

-- 向右填充，同理
rpad(str,len,str_p)
-- 对ABC向右填充o直到变为6个字符，结果为'ABCooo'
select rpad('ABC',6,'o') from dual
-- 若len小于原始字符，依然改为保留左侧len个字符
select rpad('ABC',2) from dual -- 结果为AB

instr 找字符串出现位置

效率比like高

-- 从字符串str1中找str2，从第start个字符开始找,找第n次出现的位
instr(str1,str2,start,n) -- strat和n可不填，默认为1

-- 从头开始找第2个A出现的位置，结果为5
select instr('BABBABAB','A',1,2) from dual

-- 当start为负时，从倒数第start位往前找
-- 从倒数第3位找第1个A出现的位置，结果为5
select instr('BABBABAB','A',-3) from dual

-- 找出名字含T的的员工名字
select ename from emp where instr(ename,'T')>0

substr 截取字符串

-- 对字符串str截取，从第start个字符开始，截取len个字符，返回值是一个字符串
substr(str,start,len) -- len可不填，默认截完

-- 从第2个字符开始截取3个字符，结果为BCD
select substr('ABCDEF',2,3) from dual

trunc 截断

数字截断：截断数字到指定的小数位数

-- num:要截断的数字； n：要保留的小数位
trunc(num,n)

-- n为正数时，截断到n位小数，结果为123.45
select trunc(123.4567,2) from dual

-- n不填时默认为0，即仅保留整数部分，结果为123
select trunc(123.4567) from dual

-- n为负数时，截断将小数点左边n位置0，结果: 120
select trunc(123.4567, -1) from dual

日期截断：将时间设置为当天开始的时间（00:00:00）

-- 当天 2025/5/7，只显示日期，不显示时间
select trunc(sysdate) from dual

-- 当前月份的第一天 2025/5/1
select trunc(sysdate,'month') from dual

-- 当前年份的第一天 2025/1/1
select trunc(sysdate,'year') from dual

replace 替换字符串

--将字符串str中的字符s替换成字符d
replace(str,s,d)
--显示所有员工的姓名，将字符'A'替换为'ai'
select replace(ename,'A','ai') from emp
--d可不填，默认为空，即相当于删除字符'A'
select replace(ename,'A') from emp
--脱敏（脱除敏感数据）

translate 替换字符串

-- 将字符串str中的from替换为to（其中from和to是逐个字符一一对应关系）
translate(str,from,to)

-- 简单字符替换，结果为'a234b' (1→a, 5→b)
select translate('12345','15','ab') from dual

--字符删除,结果为'He Wrd' (l和o被删除)
select translate('Hello World','lo','') from dual

--加密转换 (字母表反向映射)
select translate('SECRET','ABCDEFGHIJKLMNOPQRSTUVWXYZ','ZYXWVUTSRQPONMLKJIHGFEDCBA') from dual

数字函数

取整

-- num为要取整的数字，n为精度，可不填，默认为0，即取整数
-- n为正数时取小数点之后n位，为负数时则为小数点之前
round(num,n) 四舍五入
floor(num,n) 向下取整
ceil(num,n) 向上取整

运算

mod(num1,num2) 取num1/num2的余数
power(n,m) 求n的m次幂
sqrt(num) 返回num的平方根

转换函数

隐式转换：指Oracle在执行SQL语句时自动将数据类型转换成需要的数据类型。隐式转换可能会 导致精度损失或数据不准确，因此在使用时需要谨慎。

显示转换：指在SQL语句中使用一些函数强制将某个数据类型转换成另一个数据类型，如：

to_number 字符转换为数字 -- 正常情况下数字可以自信隐式转换，不是数字的不能使用to_number转换，结论就是一般情况不用
select * from emp where empno=to_number('7499') --数字转换的前提是看起来像数字

to_date 字符类型按一定格式转化为日期类型 -- 跟日期有关的不管是日期转字符还是字符转日期都必须带日期格式
select * from emp where hiredate=to_date(1981,'yyyy')

to_char 数字转化为字符
to_char 日期转化为字符,必须加单引号,并且区分大小写

-- to_char的其他用法
Select to_char(sysdate,'ss') from dual 取当前时间秒部分
Select to_char(sysdate,'mi') from dual 取当前时间分钟部分 
Select to_char(sysdate,'HH24') from dual 取当前时间小时部分 
Select to_char(sysdate,'DD') from dual 取当前时间日期部分 
Select to_char(sysdate,'MM') from dual 取当前时间月部分 
Select to_char(sysdate,'YYYY') from dual 取当前时间年部分 
Select to_char(sysdate,'w') from dual 取当前时间是一个月中的第几周(从1日开始算) 
Select to_char(sysdate,'ww') from dual 取当前时间是一年中的第几周（从1.1开始算） 
Select to_char(sysdate,'iw') from dual 取当前时间是一年中的第几周（按实际日历的） 
Select to_char(sysdate,'d') from dual 取当前时间是一周的第几天，从星期天开始，周六结束 Select to_char(sysdate,'day') from dual 取当前日是星期几，和数据库设置的字符集有关，会输出'Tuesday'或星期几 
Select to_char(sysdate,'ddd') from dual 当前日是一年中的第几天

日期函数

last_day(日期)：取当前日期月的最后一天

next_day(sysdate,n)：取离自己日期最近的一个周（下周） 的第几天，假如本周还未过到这一天，那么求的就是本周对应的天数
--下下周
select next_day(next_day(sysdate,2),2) from dual

add_months(日期,月)：给一个日期加上若干个月
--加一年
select add_months(sysdate,12) from dual

months_between(date1,date2)：取两个日期相差的月数

nvl 空值替换

-- 若exp1为空，返回exp2，否则返回exp1（主要两者类型要一致）
nvl(exp1,exp2)

-- 若exp1不为空，返回exp2，若exp2为空，返回exp3
-- exp2和exp3类型不同的话，exp3会转换为exp2的类型
nvl2(exp1,exp2,exp3)

练习

--1.求姓名为SMITH的员工的工作名称长度
select length(job) from emp where ename='SMITH'

--2.求名字长度为6的员工姓名，编号
select ename,empno from emp where length(ename)=6

--3.求员工的工资，格式如：姓名是：SMITH,工资是：800
select '姓名是：' || ename || '，工资是：' || sal from emp

--4.找出emp表中员工名字包含T字母的员工
select ename from emp where ename like '%T%'

--5.找出emp表里名字第4位之后含T字母的员工
select ename from emp where ename like '____%T%'

--6.找出emp表里名字倒数第3位之前含M字母的员工
select ename from emp where ename like '%M%___'

--7.求第五位是H的员工姓名
select ename from emp where ename like '____H%'

--8.求姓名中带有三个L的姓名
select ename from emp where ename like '%LLL%'

--9.将员工姓名首字母大写，其余字母小写
select initcap(ename) from emp

--10.将员工姓名首字母小写，其余字母大写
select lower(substr(ename,1,1)) || upper(substr(ename,2)) from emp

--11.查询工资是偶数的员工
select ename,sal from emp where mod(sal,2)=0

--12.求1981年2月20号入职的员工
select ename,hiredate from emp where hiredate = date '1981-02-20'

--13.求1981年入职的员工
select ename,hiredate from emp where to_char(hiredate,'yyyy')='1981'

--14.求2月份入职的员工
select ename,hiredate from emp where to_char(hiredate,'mm')='02'

--15.取当前月倒数第5天入职的员工
select ename,hiredate from emp where 
to_char(hiredate,'yyyy-mm-dd')= to_char(last_day(sysdate)-4,'yyyy-mm-dd')

--16.取每个月倒数第5天入职的员工
select ename,hiredate from emp where 
to_char(hiredate,'dd')= to_char(last_day(hiredate)-4,'dd')

--17.求下下个周三的日期
select next_day(sysdate,4)+7 from dual

--18.求3个月后的今天
select add_months(sysdate,3) from dual

--19.求每个员工的月收入
select ename,sal from emp

--20.如果comm为空就显示“No Commission”，如果不为空就显示comm的值
select nvl(to_char(comm),'No Commission') from emp

--21.求员工的一年的总收入（工资和佣金）
select sal*12+nvl(comm,0) 总薪资 from emp


--进阶额外40题

--1、使用concat 函数完成 "我是***,我的工作是***" 
select concat(concat('我是',ename),concat('我的工作是',job)) from emp

--2、对emp  ename字段首字母进行大写
select initcap(ename) from emp

--3、使用函数 求所有人员姓名的大写 首字母大写 小写 以及姓名字符长度 initcap（），upper（），lower（），length（）
select upper(ename),initcap(ename),lower(ename),length(ename) from emp

--3、从EMP表中获取job下所有职业名【distinct】（进阶需求：将工作名称都替换成中文case when）
select distinct case
       when job = 'CLERK' then '职员'
       when job = 'SALESMAN' then '销售'
       when job = 'PRESIDENT' then '董事'
       when job = 'MANAGER' then '经理'
       when job = 'ANALYST' then '分析'
       else '其他'
       end 职业
from emp

--4.instr()求ename字段 A字母位置 
select ename,instr(ename,'A') from emp

--5.显示所有员工的姓名，用“ai”替换所有“A”
select replace(ename,'A','ai') from emp

--6.以首字母小写后面大写方式显示所有员工姓名。
select lower(substr(ename,1,1)) || upper(substr(ename,2)) from emp

--7.查询员工姓名的第三个字母是A的员工姓名 
select ename from emp where ename like '__A%'

--8.显示正好为5个字符的员工的姓名。
select ename from emp where ename like '_____'

--9.求所有ename带‘L’的员工，并以L为界将L前的字母大写，之后的字母小写。
select upper(substr(ename,1,instr(ename,'L')-1)) || 'L' || lower(substr(ename,instr(ename,'L')+1))
from emp where ename like '%L%'

--10.对所有ename用空格进行长度补齐，左右两边的空格数大致相同，使其统一长度为15，（对该列数据进行去空格操作）。
select rpad(lpad(ename,length(ename)+ceil((15-length(ename))/2)),15) 姓名 from emp 

--11.在所有的名字中间使用‘+’进行补齐至18位（奇数名使用数字函数round（）将位数取整）
select ename,substr(ename,1,length(ename)-round(length(ename)/2)) || lpad(substr(ename,round(length(ename)/2)),18-round(length(ename)/2),'+') from emp

--12.将所有的ename中A开头或A结尾的记录 中的A去掉
select ename,replace(ename,'A') from emp where ename like 'A%' or ename like '%A'

--13.求所有名字，b字母出现的个数
select ename,length(ename)-length(replace(ename,'b')) from emp

--14.查找a-z字母表中a字母出现次数


--15.截取ename字段的后3位字母
select substr(ename,length(ename)-2) from emp

--16.求所有员工的名字字符长度，并按照从高到低排列，所有员工名称小写
select lower(ename) 员工, length(ename) 字符长度 from emp order by 字符长度 desc

--17.将ename和job 进行并列并在中间填充 * 使整列达到20个字符
select rpad(ename,20-length(job),'*') || job from emp

--18.显示员工姓名中最后一个字符是T的员工以及姓名第二个字是L 的员工 用instr这个语句实现 
select ename from emp where instr(ename,'T',-1)=length(ename) or instr(ename,'L',2)=2

--19.取所有员工日薪，精确到分，对后面的位数进行四舍五入。
select round(sal*12/365,2) from emp

--20.取所有员工日薪，精确到分，对小数位数进行截断，只保留整数部分
select trunc(round(sal*12/365,2)) from emp

21.求所有员工日薪的最大整数并加余数 
22.求所有员工日薪的最大小数点后2位有效数
23.求入职最早和入职最晚员工
24.求所有员工入职10年后的日期
25求员工入职的时间时星期几
26求员工到目前入职了多少月多少周多少天多少年，所有日期都四舍五入
27.员工入职时间月底是星期几
28求所有员工入职的下一个月的第一个星期一
29求当前时间的一个月零12天是几月几号
30求当前年月日时分秒数值相加之和
31.将2018年1月1日加一年零2个月 后是什么日期
32.把字符串2015-3月-18 13:13:13?转换成日期格式?转换成日期格式，并计算和系统当前时间间隔多少天。
33.找出各月倒数第2天受雇的所有员工。
34.显示所有11月份入职的员工
35.求 各个工作岗位中 工资最高的金额和最低的金额
36.求各个部门的工资总和，并列出工资数最高的金额，求标准差。
37.求各部门薪酬最大值和最小值的差值
38.统计 85年前入职的员工 和85年入职后的员工的平均工资
39. 将30号部门人员都改40号部门 人员显示
40.对员工工资进行评级 4000-5000 A级   3000-4000B级 2-3000C级 1-2000D级 1000以下的是F级
41.对各个部门各职业工资情况进行从高到低排序