Oracle常用函数集锦
1.空值处理函数
1.1 nvl
用途：将第一参数（可能的）空值转换为第二参数
典型应用场景：A表左联到B表，如果某字段可能为空时。
例：
SQL> select emp.name,nvl(dept.name,'梁山') as DEPT from emp 2 left join dept 3 on emp.dept_id=dept.id; NAME DEPT -------------------- -------------------- 鲁智深 二龙山 武松 二龙山 花荣 清风寨 周通 桃花山 李逵 梁山
建表充值：
create table emp( name varchar2(20), dept_id number(6) ); insert into emp(name,dept_id) values('鲁智深',1); insert into emp(name,dept_id) values('武松',1); insert into emp(name,dept_id) values('花荣',2); insert into emp(name,dept_id) values('李逵',4); insert into emp(name,dept_id) values('周通',3); create table dept( id number(6), name varchar2(20) ); insert into dept(id,name) values(1,'二龙山'); insert into dept(id,name) values(2,'清风寨'); insert into dept(id,name) values(3,'桃花山');
解析：李逵的dept_id=4在dept表里是没有值的，对于这种情况便统一处理为‘梁山’。
1.2 nvl2
改函数是对nvl的扩展，它有三个参数，当第一参数不为空是取第二参数，为空时取第三参数。
示例：
select nvl2(b.status,'Failed','Succeeded') as sts from ( select decode(a.stat,0,null,1,'s') as status from ( select mod(level,2) as stat from dual connect by level<5 ) a ) b
执行结果：
luna@ORCL>select nvl2(b.status,'Failed','Succeeded') as sts 2 from 3 ( 4 select decode(a.stat,0,null,1,'s') as status 5 from 6 ( select mod(level,2) as stat 7 from dual 8 connect by level<5 9 ) a 10 ) b; STS --------- Failed Succeeded Failed Succeeded
1.3 nullif 比较两参数值，相等返回空，不相等返回第一参数值 (此函数相对少见)
示例：
luna@ORCL>select nullif(5/2,2) from dual; NULLIF(5/2,2) ------------- 2.5 已选择 1 行。 luna@ORCL>select nullif(5/2,2.5) from dual; NULLIF(5/2,2.5) --------------- 已选择 1 行。
1.4 coalesce 取多个参数中第一个不为空的值
比如有这样一张表：
create table emp826( name nvarchar2(20), hire_date timestamp, update_date timestamp, create_date timestamp);
这样充值：
insert into emp826(name,hire_date,update_date,create_date) values('newton',to_date('2022-01-01','yyyy-MM-dd'),to_date('2022-04-01','yyyy-MM-dd'),to_date('2022-08-01','yyyy-MM-dd')); insert into emp826(name,hire_date,update_date,create_date) values('gause',null,to_date('2022-04-01','yyyy-MM-dd'),to_date('2022-08-01','yyyy-MM-dd')); insert into emp826(name,hire_date,update_date,create_date) values('einstain',null,null,to_date('2022-08-01','yyyy-MM-dd'));
可以看到，有人的hire_date是空，这时该取update_date；有人的update_date还是空，这时该取create_date.
用coalesce就可以实现这个逻辑：
SQL：
select name,to_char(coalesce(hire_date,update_date,create_date),'yyyy-MM-dd') as hdate from emp826;
执行效果：
luna@ORCL>select name,to_char(coalesce(hire_date,update_date,create_date),'yyyy-MM-dd') as hdate from emp826; NAME HDATE ---------------------------------------- ---------- newton 2022-01-01 gause 2022-04-01 einstain 2022-08-01
2.分支函数
2.1 decode
语法：decode(列，匹配内容1，显示值1，匹配内容2，显示值2，...，匹配内容N，显示值N，全不匹配时的显示值);
这个函数在分支数少而简单时可以替代case when
luna@ORCL>select decode(trunc(dbms_random.value(1,6)),1,'waiting',2,'runnable',3,'running',4,'failed',5,'interrupted',6,'succeeded','unclassified') from dual; DECODE(TRUNC ------------ interrupted 已选择 1 行。 luna@ORCL>select decode(trunc(dbms_random.value(1,6)),1,'waiting',2,'runnable',3,'running',4,'failed',5,'interrupted',6,'succeeded','unclassified') from dual; DECODE(TRUNC ------------ runnable 已选择 1 行。
2.2 case when
示例：下面SQL将1，2，3三种状态转化为对应的running、failed、succeeded。
luna@ORCL>select case when a.val=1 then 'running' when a.val=2 then 'failed' else 'succeeded' end as status 2 from 3 (select trunc(dbms_random.value(1,3)) as val from dual) a; STATUS --------- running 已选择 1 行。
此例中case when可以被decode取代，但对于复杂判断还是case when更顺手些。如
select tb.task_id, decode(tc.alls_flg,null,0,1) as sum, decode(tb.alls_flg,tc.alls_flg,1,0) as succeeded, case when tc.alls_flag is null then 0 when tb.alls_flg=tc.alls_flg then 0 else 1 end as fialed from tb left join tc on tb.job_ids=tc.job_ids
3.转换函数
3.1 to_char
示例：比如将timestamp类型数据转化成我们熟悉的年月日时分秒格式
luna@ORCL>select to_char(sysdate,'yyyy-MM-dd hh:mi:ss') as time from dual; TIME ------------------- 2022-08-26 02:54:03
还可以来一个比较特别的例子：
luna@ORCL>select to_char(sysdate,'fmDdspth / Month Year fmHH:MI:SS') from dual;
TO_CHAR(SYSDATE,'FMDDSPTH/MONTHYEARFMHH:MI:SS')
---------------------------------------------------------------------------
Twenty-Sixth / 8月 Twenty Twenty-Two 04:38:31
已选择 1 行。
to_char还可以将数字转化为货币格式，比如有这样一张表：
create table sal0826( year number(4), total number(12) );
其数据是：
insert into sal0826(year,total) values('2021','254000'); insert into sal0826(year,total) values('2022','286000'); insert into sal0826(year,total) values('2023','360000');
将其转化为美元可以这样做：
luna@ORCL>select year,to_char(total,'$999,999.00') as annual_salary from sal0826; YEAR ANNUAL_SALAR ---------- ------------ 2021 $254,000.00 2022 $286,000.00 2023 $360,000.00
或是人民币格式可以是：
luna@ORCL>select year,to_char(total,'L999,999.00') as annual_salary from sal0826; YEAR ANNUAL_SALARY ---------- --------------------- 2021 ￥254,000.00 2022 ￥286,000.00 2023 ￥360,000.00
中国喜欢以万为单位，那么改变一下逗号位置即可：
luna@ORCL>select year,to_char(total,'L99,9999.00') as annual_salary from sal0826; YEAR ANNUAL_SALARY ---------- --------------------- 2021 ￥25,4000.00 2022 ￥28,6000.00 2023 ￥36,0000.00 已选择3行。
用于计算一天前，一周前，一月前，一年前的日期可以这样做：
select to_char((sysdate-interval'1'day),'yyyy-mm-dd') as yesterday, to_char((sysdate-interval'7'day),'yyyy-mm-dd') as weekago, to_char((sysdate-interval'1'month),'yyyy-mm-dd') as monthago, to_char((sysdate-interval'1'year),'yyyy-mm-dd') as yearago from dual
执行效果：
SQL> select 2 to_char((sysdate-interval'1'day),'yyyy-mm-dd') as yesterday, 3 to_char((sysdate-interval'7'day),'yyyy-mm-dd') as weekago, 4 to_char((sysdate-interval'1'month),'yyyy-mm-dd') as monthago, 5 to_char((sysdate-interval'1'year),'yyyy-mm-dd') as yearago 6 from dual; YESTERDAY WEEKAGO MONTHAGO -------------------- -------------------- -------------------- YEARAGO -------------------- 2022-08-27 2022-08-21 2022-07-28 2021-08-28
3.2 to_date
示例：将字符串形式的建国日转化为timestamp形式。
luna@ORCL>select to_date('1949-10-1','yyyy-MM-dd') as national_day from dual; NATIONAL_DAY -------------- 01-10月-49
利用to_date还可以得知两个日期间的天数：
select trunc(to_date('2022-09-05','yyyy-mm-dd')-to_date('2022-09-01','yyyy-mm-dd'))+1 from dual;
执行：
SQL> select trunc(to_date('2022-09-05','yyyy-mm-dd')-to_date('2022-09-01','yyyy-mm-dd'))+1 from dual; TRUNC(TO_DATE('2022-09-05','YYYY-MM-DD')-TO_DATE('2022-09-01','YYYY-MM-DD'))+1 ------------------------------------------------------------------------------ 5
辅之以connect by，可以得到两个日期间隔间的连续日期：
select to_char(to_date('2018-09-01', 'yyyy-mm-dd') + ROWNUM - 1, 'yyyy-mm-dd') as dt from dual connect by rownum<=trunc(to_date('2022-09-05','yyyy-mm-dd')-to_date('2022-09-01','yyyy-mm-dd'))+1
执行：
SQL> select to_char(to_date('2018-09-01', 'yyyy-mm-dd') + ROWNUM - 1, 'yyyy-mm-dd') as dt 2 from dual 3 connect by rownum<=trunc(to_date('2022-09-05','yyyy-mm-dd')-to_date('2022-09-01','yyyy-mm-dd'))+1; DT -------------------- 2018-09-01 2018-09-02 2018-09-03 2018-09-04 2018-09-05
3.3 to_number
将字符串变成数字，如：
luna@ORCL>select to_number('1.25')*4 from dual; TO_NUMBER('1.25')*4 ------------------- 5
还可以将特定格式字符串还原成数字
luna@ORCL>select TO_NUMBER('$123,456.78','$999999.99') from dual; TO_NUMBER('$123,456.78','$999999.99') ------------------------------------- 123456.78 已选择 1 行。
还可以将十六进制数转化为十进制
luna@ORCL>select to_number('AF','XX') from dual; TO_NUMBER('AF','XX') -------------------- 175
to_number和to_char的组合对取日期的一部分有奇效，如：
SQL：
select to_number(to_char(sysdate,'hh24')) as hour, --小时 to_number(to_char(sysdate,'mi')) as minute, --分 to_number(to_char(sysdate,'ss')) as second, --秒 to_number(to_char(sysdate,'dd')) as dday, --日 to_number(to_char(sysdate,'mm')) as month, --月 to_number(to_char(sysdate,'yyyy')) as year, --年 to_number(to_char(sysdate,'ddd')) as nday --年内第几天 from dual
执行效果：
SQL> select 2 to_number(to_char(sysdate,'hh24')) as hour, --小时 3 to_number(to_char(sysdate,'mi')) as minute, --分 4 to_number(to_char(sysdate,'ss')) as second, --秒 5 to_number(to_char(sysdate,'dd')) as dday, --日 6 to_number(to_char(sysdate,'mm')) as month, --月 7 to_number(to_char(sysdate,'yyyy')) as year, --年 8 to_number(to_char(sysdate,'ddd')) as nday --年内第几天 9 from dual; HOUR MINUTE SECOND DDAY MONTH YEAR NDAY ---------- ---------- ---------- ---------- ---------- ---------- ---------- 22 45 16 26 8 2022 238
如果要判断字段是由数字组成可以使用正则表达式函数 regexp_like(column,'^[0-9]+$')
比如有这样一张表：
create table emp827( name nvarchar2(20), sn varchar2(20) );
这样充值：
insert into emp827(name,sn) values('Andy','9527'); insert into emp827(name,sn) values('Bill','9528'); insert into emp827(name,sn) values('Cindy','a123'); insert into emp827(name,sn) values('Douglas','34.56');
使用regexp_like的语句为：
select * from emp827 where regexp_like(sn,'^[0-9]+$');
执行效果是：
SQL> select * from emp827 where regexp_like(sn,'^[0-9]+$'); NAME SN -------------------- -------------------- Andy 9527 Bill 9528
4.日期函数
4.1 months_between 用于计算两个timestamp之间的月数
示例：取当前日期和120天前的日期的月份差值
luna@ORCL>select months_between(sysdate,sysdate-120) from dual; MONTHS_BETWEEN(SYSDATE,SYSDATE-120) ----------------------------------- 3.93548387
4.2 add_months 在timestamp上进行月份添加
示例：在timestamp上加一天直接+1即可，如果要增加一个月可以用add_months,它能省去判断大小月闰月的麻烦。
luna@ORCL>select to_char(sysdate+1,'yyyy-MM-dd') from dual; TO_CHAR(SY ---------- 2022-08-27 luna@ORCL>select to_char(add_months(sysdate,1),'yyyy-MM-dd') from dual; TO_CHAR(AD ---------- 2022-09-26
4.3 next_day 返回指定日期的下一个星期几
示例：今天是2022-08-26，如下SQL能显示下周一是2022-08-29。注意第二参数是1~7，代表周日、周一到周六
luna@ORCL>select to_char(next_day(sysdate,2),'yyyy-MM-dd') from dual; TO_CHAR(NE ---------- 2022-08-29
4.4 last_day 返回指定日期所在月份的最后一天
示例：今天是2022-08-26，如下SQL能显示本月最后一天是2022-08-31
luna@ORCL>select to_char(last_day(sysdate),'yyyy-MM-dd') from dual; TO_CHAR(LA ---------- 2022-08-31
示例：今天是2022-08-26，如下SQL能显示距月底还有多少天（等于计算距离发工资日还有多少天）
luna@ORCL>select last_day(sysdate)-sysdate as leftDays from dual; LEFTDAYS ---------- 5
4.5 trunc 将指定日期截取到年或月或日
示例：取当前日期所在月份的第一天。
luna@ORCL>select to_char(trunc(sysdate,'month'),'yyyy-MM-dd') as firstday from dual; FIRSTDAY ---------- 2022-08-01
示例：取当前日期所在年份的第一天
luna@ORCL>select to_char(trunc(sysdate,'year'),'yyyy-MM-dd') as firstday from dual; FIRSTDAY ---------- 2022-01-01
示例：计算22年圣诞节离当前日期2022-08-26还有多少天
luna@ORCL>select trunc(to_date('2022-12-25','yyyy-MM-dd')-sysdate) as days from dual; DAYS ---------- 120
示例：取今天是本月的第几天，即取日期中的日子 ，比如今日是2022-08-26，想要得到26这个值
luna@ORCL>select trunc(sysdate-trunc(sysdate,'month'))+1 as day from dual; DAY ---------- 26 已选择 1 行。
4.6 extract 从date或是timestamp字段中抽取出特定值。
如：
select extract(year from sysdate) as year, extract(month from sysdate) as month, extract(day from sysdate) as day from dual;
执行效果：
SQL> select 2 extract(year from sysdate) as year, 3 extract(month from sysdate) as month, 4 extract(day from sysdate) as day 5 from dual; YEAR MONTH DAY ---------- ---------- ---------- 2022 8 27
又如：
select extract(year from timestamp'2022-08-27 14:25:25') as year, extract(month from timestamp'2022-08-27 14:25:25') as month, extract(day from timestamp'2022-08-27 14:25:25') as day, extract(hour from timestamp'2022-08-27 14:25:25') as hour, extract(minute from timestamp'2022-08-27 14:25:25') as minute, extract(second from timestamp'2022-08-27 14:25:25') as second from dual;
执行效果：
SQL> select 2 extract(year from timestamp'2022-08-27 14:25:25') as year, 3 extract(month from timestamp'2022-08-27 14:25:25') as month, 4 extract(day from timestamp'2022-08-27 14:25:25') as day, 5 extract(hour from timestamp'2022-08-27 14:25:25') as hour, 6 extract(minute from timestamp'2022-08-27 14:25:25') as minute, 7 extract(second from timestamp'2022-08-27 14:25:25') as second 8 from dual; YEAR MONTH DAY HOUR MINUTE SECOND ---------- ---------- ---------- ---------- ---------- ---------- 2022 8 27 14 25 25
这个函数还可以告诉我们两个timestamp差值之间间隔了多少天。
SQL：
select extract(day from a.dt) as day from ( select (timestamp'2022-08-28 08:30:00'-timestamp'2021-09-13 08:30:00') as dt from dual ) a
执行：
SQL> select 2 extract(day from a.dt) as day 3 from 4 ( 5 select (timestamp'2022-08-28 08:30:00'-timestamp'2021-09-13 08:30:00') as dt from dual 6 ) a; DAY ---------- 349
这个用于计算高考中考倒计时，距XX纪念日有N天这样的例子很方便。
5.数学函数
5.1 round 对小数点后的数字进行四舍五入
示例：对2.5进行四舍五入
luna@ORCL>select round(5/2) from dual; ROUND(5/2) ---------- 3
示例：对1.25进行四舍五入
luna@ORCL>select round(5/4) from dual; ROUND(5/4) ---------- 1
示例：将圆周率四舍五入到第四位
luna@ORCL>select round(3.14159,4) from dual; ROUND(3.14159,4) ---------------- 3.1416
5.2 trunc 截断小数点后的数字
示例：对2.5进行截断
luna@ORCL>select trunc(5/2) from dual; TRUNC(5/2) ---------- 2
示例：对1.25进行截断
luna@ORCL>select trunc(5/4) from dual; TRUNC(5/4) ---------- 1
示例：对圆周率第四位后舍弃
luna@ORCL>select trunc(3.14159,4) from dual; TRUNC(3.14159,4) ---------------- 3.1415
5.3 mod 取余数
示例：求10 % 3
luna@ORCL>select mod(10,3) from dual; MOD(10,3) ---------- 1
6.字符函数
6.1 lower/upper/initcap 小写化、大写化、首字母大写化
luna@ORCL>select lower('albert'),upper('albert'),initcap('albert') from dual; LOWER( UPPER( INITCA ------ ------ ------ albert ALBERT Albert
6.2 length 取字符串长度
luna@ORCL>select length('albert') from dual; LENGTH('ALBERT') ---------------- 6
6.2 instr 子串位置查找
luna@ORCL>select instr('albert','er') from dual; INSTR('ALBERT','ER') -------------------- 4 已选择 1 行。 luna@ORCL>select instr('albert','e') from dual; INSTR('ALBERT','E') ------------------- 4 已选择 1 行。
持续更新中.
【推荐】国内首个AI IDE，深度理解中文开发场景，立即下载体验Trae
【推荐】编程新体验，更懂你的AI，立即体验豆包MarsCode编程助手
【推荐】抖音旗下AI助手豆包，你的智能百科全书，全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell：AI 加持，快人一步
· 无需6万激活码！GitHub神秘组织3小时极速复刻Manus，手把手教你使用OpenManus搭建本
· C#/.NET/.NET Core优秀项目和框架2025年2月简报
· Manus爆火，是硬核还是营销？
· 终于写完轮子一部分：tcp代理 了，记录一下
· 【杭电多校比赛记录】2025“钉耙编程”中国大学生算法设计春季联赛（1）
2019-08-16 【高中数学/函数/零点】求分段函数f(x)=x^2-4x+2(x>=1) f(x)=|lg(1-x)| (x<1)的零点个数