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;