1、数值型常用函数
(1)ceil大于或等于数值N的最小整数
select ceil(10.6) from dual;
(2)floor小于等于数值n的最大整数
select floor(9.9) from dual;
(3)求余数
select mod(8,5) from dual;
(4)求m得n 次方
select power(4,2) from dual;
(5)四舍五入
select round(123,456) FROM dual;
select round(123.678) from dual;
(6)sign(n) 若n=0,则返回0,否则,n>0,则返回1,n<0,则返回-1
select sign(8) from dual;
(7)开平方
select sqrt(16) from dual;
2、常用字符函数
(1)initcap(char) 把每个字符串的第一个字符换成大写
select initcap(‘mr.ecop’) from dual;
(2)切换大小写
--lower(char) 整个字符串换成小写
select lower(‘SJG’) from dual;
--UPPER(string)整个字符串换成大写
SELECT UPPER(‘computer’) FROM dual;
(3)replace(char,str1,str2)字符串中所有str1换成str2
select replace(‘abc’,‘b’,‘ABC’) from dual;
(4)substr(char,m,n) 取出从m字符开始的n个字符的子串
SELECT substr(‘abcdef’,2,2) from dual;
(5)length(char) 求字符串的长度
SELECT length (‘ABCDEF’) from dual;
(6)|| 并置运算符
select ‘abc’ || ‘ABC’ from dual;
(7)字符串截取
select substr(‘abcdef’,1,3) from dual
(8)查找子串位置
select instr(‘abcfdgfdhd’,‘fd’) from dual
(9)字符串连接
select ‘SJG’||‘hello world’ from dual;
(10)去掉字符串中的空格
select ltrim(’ abc’) s1,rtrim(‘abc ‘) s2,trim(’ abc ‘) s3 from dual
--去掉前导和后缀
select trim(leading A from AAAAXGBDBAAA ) a, trim(trailing A from AAAXGBDBAAA) b,trim(A from AAAXGBDBAAA) c from dual;
(11)返回字符串首字母的Ascii值
select ascii(‘a’) from dual
(12)返回ascii值对应的字母
select chr(97) from dual
(13)translate
select translate(‘abc’,‘b’,‘xx’) from dual; --axc 注:x是1位
(14)lpad 左添充 rpad 右填充
select lpad(‘func’,6,’=’) s1, rpad(‘func’,6,’-’) s2 from dual; – ==func , func–
(15) decode[实现if …then 逻辑] 注:第一个是表达式,最后一个是不满足任何一个条件的值
select deptno,decode(deptno,10,'1',20,'2',30,'3','其他') from dept;
select seed,account_name,decode(seed,111,1000,200,2000,0) from t_userInfo--//如果seed为111,则取1000;为200,取2000;其它取0
select seed,account_name,decode(sign(seed-111),1,'big seed',-1,'little seed','equal seed') from t_userInfo--//如果seed>111,则显示大;为200,则显示小;其它则显示相等
3、日期型函数
--(1)sysdate 当前日期和时间
SELECT sysdate from dual;
--(2)add_months(d,n) 当前日期d后推n个月
select add_months(SYSDATE,2) from dual;
--(3)months_between(d,n) 日期d和n相差月数
select months_between(sysdate ,to_date('20200525','YYYYMMDD')) from dual;
--(4)last_day 本月最后一天
select last_day(sysdate) from dual;
--(5)next_day(d,day) d后第一周指定day的日期
select next_day(sysdate,8) from dual;
4、ORACLE日期时间函数大全
24小时格式下时间范围为: 0:00:00 - 23:59:59....
12小时格式下时间范围为: 1:00:00 - 12:59:59 ....
特殊格式的日期型函数
--(1)Y或YY或YYY 年的最后一位,两位,三位
select to_char(sysdate,'YYY') from dual;
--(2)Q季度,1-3月为第一季度
select to_char(sysdate,'Q') from dual;
--(3)MM月份数
select to_char(sysdate,'MM') from dual;
--(4)RM月份的罗马表示
select to_char(sysdate,'RM') from dual;
--(5)month 用英文字符表示的月份名
select to_char(sysdate,'month') from dual;
--(6)ww或WW 当年第几周
select to_char(sysdate,'ww') from dual;
--(7)w或W本月第几周
select to_char(sysdate,'W') from dual;
--(8)DDD 当年第几天,一月一日为001 ,二月一日032
select to_char(sysdate,'DDD') from dual;
--(9)DD当月第几天
select to_char(sysdate,'DD') from dual;
--(10)D 周内第几天
select to_char(sysdate,'D') from dual;
--(11)DY 周内第几天缩写
select to_char(sysdate,'DY') from dual;
--(12)hh12或HH12 12小时制小时数
select to_char(sysdate,'hh12') from dual;
--(13)hh24或HH24 24小时制小时数
select to_char(sysdate,'HH24') from dual;
--(14)Mi或mi分钟数
select to_char(sysdate,'mi') from dual;
--(15)ss秒数
select to_char(sysdate,'ss') from dual;
--(16)改时间格式
select to_char(sysdate,'YYYY-MM-DD HH24:mi:ss') from dual;
–(17)round舍入到最接近的日期
select sysdate S1,
round(sysdate) S2 ,
round(sysdate,'year') YEAR,
round(sysdate,'month') MONTH ,
round(sysdate,'day') DAY from dual
–(18)trunc[截断到最接近的日期,单位为天] ,返回的是日期类型
select sysdate S1,
trunc(sysdate) S2, //返回当前日期,无时分秒
trunc(sysdate,'year') YEAR, //返回当前年的1月1日,无时分秒
trunc(sysdate,'month') MONTH , //返回当前月的1日,无时分秒
trunc(sysdate,'day') DAY //返回当前星期的星期天,无时分秒
from dual
–(19)返回日期列表中最晚日期
select greatest('01-1月-04','04-1月-04','10-2月-04') from dual
–(20)计算时间差
注:oracle时间差是以天数为单位,所以换算成年月,日
select floor(to_number(sysdate-to_date('2019-05-26 00:37:38','yyyy-mm-dd hh24:mi:ss'))/365) as spanYears from dual //时间差-年
select ceil(moths_between(sysdate-to_date('2019-05-26 00:37:38','yyyy-mm-dd hh24:mi:ss'))) as spanMonths from dual //时间差-月
select floor(to_number(sysdate-to_date('2019-05-26 00:37:38','yyyy-mm-dd hh24:mi:ss'))) as spanDays from dual //时间差-天
select floor(to_number(sysdate-to_date('2019-05-26 00:37:38','yyyy-mm-dd hh24:mi:ss'))*24) as spanHours from dual //时间差-时
select floor(to_number(sysdate-to_date('2019-05-26 00:37:38','yyyy-mm-dd hh24:mi:ss'))*24*60) as spanMinutes from dual //时间差-分
select floor(to_number(sysdate-to_date('2019-05-26 00:37:38','yyyy-mm-dd hh24:mi:ss'))*24*60*60) as spanSeconds from dual //时间差-秒
–(21)更新时间
注:oracle时间加减是以天数为单位,设改变量为n,所以换算成年月,日
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),to_char(sysdate+n*365,'yyyy-mm-dd hh24:mi:ss') as newTime from dual //改变时间-年
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),add_months(sysdate,n) as newTime from dual //改变时间-月
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),to_char(sysdate+n,'yyyy-mm-dd hh24:mi:ss') as newTime from dual //改变时间-日
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),to_char(sysdate+n/24,'yyyy-mm-dd hh24:mi:ss') as newTime from dual //改变时间-时
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),to_char(sysdate+n/24/60,'yyyy-mm-dd hh24:mi:ss') as newTime from dual //改变时间-分
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),to_char(sysdate+n/24/60/60,'yyyy-mm-dd hh24:mi:ss') as newTime from dual //改变时间-秒
–(22)查找月的第一天,最后一天
SELECT Trunc(Trunc(SYSDATE, 'MONTH') - 1, 'MONTH') First_Day_Last_Month,
Trunc(SYSDATE, 'MONTH') - 1 / 86400 Last_Day_Last_Month,
Trunc(SYSDATE, 'MONTH') First_Day_Cur_Month,
LAST_DAY(Trunc(SYSDATE, 'MONTH')) + 1 - 1 / 86400 Last_Day_Cur_Month
FROM dual;