oracle常用函数使用汇总.doc

一.函数简单说明字符函数 1、select upper(coolszy) from dual; 将小写字母转换成大写，dual 为一虚表 2、select lower(KUKA) from dual; 将大写字母转换成小写 3、select initcap(kuka) from dual; 将首字母大写 4、select concat(Hello, world) from dual; 连接字符串，但没有|好用 5、select substr(hello,1,3) from dual; 截取字符串 6、select length(hello) from dual; 求字符串长度 7、select replace(hello,l,x) from dual; 替换字符串 8、select substr(hello,-3,3) from dual; 截取后三位 数值函数 9、select round(789.536) from dual; 四舍五入，舍去小数 10、select round(789.536,2) from dual; 保留两位小数 11、select round(789.536,-1) from dual; 对整数进行四舍五入 12、select trunc(789.536) from dual; 舍去小数，但不进位 13、select trunc(789.536,2) from dual; 14、select trunc(789.536,-2) from dual; 15、select mod(10,3) from dual; 返回10%3的结果 日期函数 16、select sysdate from dual; 返回当前日期 17、select months_between(sysdate,to_date(20120101,yyyymm) from dual; 返回之间的月数 18、select add_months(sysdate,4) from dual; 在日期上加上月数 19、select next_day(sysdate,星期一) from dual; 求下一个星期一 20、select last_day(sysdate) from dual; 求本月的最后一天 转换函数 30、select to_char(sysdate,yyyy) year,to_char(sysdate,mm),to_char(sysdate,dd) from dual; 31、select to_char(sysdate,yyyy-mm-dd) from dual; 32、select to_char(sysdate,fmyyyy-mm-dd) from dual; 取消月 日 前面的0 33、select to_char(20394,99,999) from dual; 分割钱 9表示格式 34、select to_char(2034,L99,999) from dual; 加上钱币符号 35、select to_number(123)*to_number(2) from dual; 36、select to_date(1988-07-04,yyyy-mm-dd) from dual; 通用函数 37、select nvl(null,0) from dual; 如果为null，则用0代替 38、select decode(1,1,内容是1,2,内容是2,3,内容是3) from dual; 类似于 switch.case. 二.日期函数的应用1、取得当前日期是本月的第几周 SQL select to_char(sysdate,YYYYMMDD W HH24:MI:SS) from dual;TO_CHAR(SYSDATE,YY-20030327 4 18:16:09SQL select to_char(sysdate,W) from dual;2、取得当前日期是一个星期中的第几天,注意星期日是第一天 SQL select sysdate,to_char(sysdate,D) from dual;SYSDATE T- -27-MAR-03 5 类似: select to_char(sysdate,yyyy) from dual; -年select to_char(sysdate,Q from dual; -季select to_char(sysdate,mm) from dual; -月select to_char(sysdate,dd) from dual; -日ddd 年中的第几天WW 年中的第几个星期W 该月中第几个星期D 周中的星期几hh 小时(12)hh24 小时(24)Mi 分ss 秒 3、取当前日期是星期几中文显示: SQL select to_char(sysdate,day) from dual;TO_CHAR(SYSDATE,DAY)-星期四 4、假如一个表在一个date类型的字段上面建立了索引，如何使用 alter session set NLS_DATE_FORMAT=YYYY-MM-DD HH24:MI:SS5、得到当前的日期select sysdate from dual; 6、得到当天凌晨0点0分0秒的日期select trunc(sysdate) from dual; - 得到这天的最后一秒select trunc(sysdate) + 0.99999 from dual; - 得到小时的具体数值select trunc(sysdate) + 1/24 from dual; select trunc(sysdate) + 7/24 from dual; 7、得到明天凌晨0点0分0秒的日期 select trunc(sysdate+1) from dual; select trunc(sysdate)+1 from dual;8、本月一日的日期select trunc(sysdate,mm) from dual;9、得到下月一日的日期select trunc(add_months(sysdate,1),mm) from dual; 10、返回当前月的最后一天?select last_day(sysdate) from dual; select last_day(trunc(sysdate) from dual;select trunc(last_day(sysdate) from dual;select trunc(add_months(sysdate,1),mm) - 1 from dual;11、得到一年的每一天select trunc(sysdate,yyyy)+ rn -1 date0from(select rownum rn from all_objectswhere rownum366);12、今天是今年的第N天SELECT TO_CHAR(SYSDATE,DDD) FROM DUAL; 13、如何在给现有的日期加上2年select add_months(sysdate,24) from dual; 14、判定某一日子所在年分是否为润年select decode(to_char(last_day(trunc(sysdate,y)+31),dd),29,闰年,平年) from dual; 15、判定两年后是否为润年select decode(to_char(last_day(trunc(add_months(sysdate,24),y)+31),dd),29,闰年,平年) from dual;16、得到日期的季度select ceil(to_number(to_char(sysdate,mm)/3) from dual; select to_char(sysdate, Q) from dual;17、上个月第一天select add_months(last_day(add_months(sysdate,-1)+1,-1) from dual;18、上个月最后一天select last_day(add_months(sysdate,-1) from dual;SELECT to_char(add_months(last_day(sysdate),-1),YYYY-MM-DD) from dual;19、extract()函数使用方法extract()函数从oracle 9i中引入,用于从一个date或者interval类型中截取到特定的部分/语法如下： EXTRACT ( YEAR | MONTH | DAY | HOUR | MINUTE | SECOND | TIMEZONE_HOUR | TIMEZONE_MINUTE | TIMEZONE_REGION | TIMEZONE_ABBR FROM date_value | interval_value ) a我们只可以从一个date类型中截取 year,month,day(date日期的格式为yyyy-mm-dd); SELECT EXTRACT(YEAR FROM SYSDATE) FROM DUAL; return Current YearSELECT EXTRACT(MONTH FROM SYSDATE) FROM DUAL; return Current MonthSELECT EXTRACT(DAY FROM SYSDATE) FROM DUAL; return Current Dayselect extract(year from date2011-05-17) year from dual; YEAR - 2011 select extract(month from date2011-05-17) month from dual; MONTH - 5 select extract(day from date2011-05-17) day from dual; DAY - 17 b我们只可以从一个 timestamp with time zone 的数据类型中截取TIMEZONE_HOUR和TIMEZONE_MINUTE; SELECT EXTRACT(HOUR FROM SYSDATE) FROM DUAL;Error Correct:SELECT EXTRACT(HOUR FROM TIMESTAMP 2005-10-10 10:10:10) FROM DUAL;SELECT EXTRACT(MINUTE FROM SYSDATE) FROM DUAL;Error Correct:SELECT EXTRACT(MINUTE FROM TIMESTAMP 2005-10-10 10:10:10) FROM DUAL;c获取两个日期之间的具体时间间隔，extract函数是最好的选择 select extract(day from dt2-dt1) day ,extract(hour from dt2-dt1) hour ,extract(minute from dt2-dt1) minute ,extract(second from dt2-dt1) second from ( select to_timestamp(2011-02-04 15:07:00,yyyy-mm-dd hh24:mi:ss) dt1 ,to_timestamp(2011-05-17 19:08:46,yyyy-mm-dd hh24:mi:ss) dt2 from dual) / DAY HOUR MINUTE SECOND - - - - 102 4 1 46 - select extract(year from systimestamp) year ,extract(month from systimestamp) month ,extract(day from systimestamp) day ,extract(minute from systimestamp) minute ,extract(second from systimestamp) second ,extract(timezone_hour f