Oracle常用函数

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;