Oracle常用函数1.docx

Oracle常用函数1.docx

《Oracle常用函数1.docx》由会员分享，可在线阅读，更多相关《Oracle常用函数1.docx（13页珍藏版）》请在冰豆网上搜索。

Oracle常用函数1

oracle常用函数汇总

感谢sou6的投递时间：

2013-08-02来源：

三联教程　

以下是对oracle中的常用函数进行了汇总介绍，需要的朋友可以过来参考下

一、运算符

算术运算符：

+-*/可以在select语句中使用

连接运算符：

||selectdeptno||dnamefromdept; 

比较运算符：

>>==!

=<<=likebetweenisnullin

逻辑运算符：

notandor 

集合运算符：

intersect，union，unionall，minus 

要求：

对应集合的列数和数据类型相同

    查询中不能包含long列

    列的标签是第一个集合的标签

    使用orderby时，必须使用位置序号，不能使用列名

例：

集合运算符的使用：

复制代码 代码如下:

intersect，union，unionall，minus 

select*fromempintersectselect*fromempwheredeptno=10;

select*fromempminusselect*fromempwheredeptno=10;

select*fromempwheredeptno=10unionselect*fromempwheredeptnoin（10,20）;--不包括重复行 

select*fromempwheredeptno=10unionallselect*fromempwheredeptnoin（10,20）;--包括重复行

二.ORACLE日期时间函数大全 

  TO_DATE格式（以时间:

2007-11-02  13:

45:

25为例）

       Year:

       yytwodigits两位年               显示值:

07

       yyythreedigits三位年               显示值:

007

       yyyyfourdigits四位年               显示值:

2007

       Month:

       mm   number    两位月             显示值:

11

       mon   abbreviated字符集表示         显示值:

11月,若是英文版,显示nov     

       monthspelledout字符集表示         显示值:

11月,若是英文版,显示november 

       Day:

       dd   number        当月第几天       显示值:

02

       ddd   number        当年第几天       显示值:

02

       dy   abbreviated当周第几天简写   显示值:

星期五,若是英文版,显示fri

       day   spelledout  当周第几天全写   显示值:

星期五,若是英文版,显示friday        

       ddspthspelledout,ordinaltwelfth 

             Hour:

             hh   twodigits12小时进制           显示值:

01

             hh24twodigits24小时进制           显示值:

13

             Minute:

             mi   twodigits60进制               显示值:

45

             Second:

             ss   twodigits60进制               显示值:

25

             其它

             Q    digit        季度                 显示值:

4

             WW   digit        当年第几周           显示值:

44

             W   digit         当月第几周           显示值:

1

       24小时格式下时间范围为：

0:

00:

00-23:

59:

59....      

       12小时格式下时间范围为：

1:

00:

00-12:

59:

59.... 

1.日期和字符转换函数用法（to_date,to_char）

selectto_char（sysdate,'yyyy-mm-ddhh24:

mi:

ss'）asnowTimefromdual;  //日期转化为字符串   

selectto_char（sysdate,'yyyy'）asnowYear  fromdual;  //获取时间的年   

selectto_char（sysdate,'mm'）   asnowMonthfromdual;  //获取时间的月   

selectto_char（sysdate,'dd'）   asnowDay   fromdual;  //获取时间的日   

selectto_char（sysdate,'hh24'）asnowHour  fromdual;  //获取时间的时   

selectto_char（sysdate,'mi'）   asnowMinutefromdual;  //获取时间的分   

selectto_char（sysdate,'ss'）   asnowSecondfromdual;  //获取时间的秒

selectto_date（'2004-05-0713:

23:

44','yyyy-mm-ddhh24:

mi:

ss'）   fromdual//

2. selectto_char（to_date（222,'J'）,'Jsp'）fromdual      

   显示TwoHundredTwenty-Two  

3.求某天是星期几      

  selectto_char（to_date（'2002-08-26','yyyy-mm-dd'）,'day'）fromdual;      

  星期一      

  selectto_char（to_date（'2002-08-26','yyyy-mm-dd'）,'day','NLS_DATE_LANGUAGE=American'）fromdual;      

  monday      

  设置日期语言      

  ALTERSESSIONSETNLS_DATE_LANGUAGE='AMERICAN';      

  也可以这样      

  TO_DATE（'2002-08-26','YYYY-mm-dd','NLS_DATE_LANGUAGE=American'）      

4.两个日期间的天数      

   selectfloor（sysdate-to_date（'20020405','yyyymmdd'））fromdual;  

5.时间为null的用法      

  selectid,active_datefromtable1      

  UNION      

  select1,TO_DATE（null）fromdual;      

   注意要用TO_DATE（null）      

6.月份差   

  a_datebetweento_date（'20011201','yyyymmdd'）andto_date（'20011231','yyyymmdd'）      

  那么12月31号中午12点之后和12月1号的12点之前是不包含在这个范围之内的。

  所以，当时间需要精确的时候，觉得to_char还是必要的 

7.日期格式冲突问题      

   输入的格式要看你安装的ORACLE字符集的类型,比如:

US7ASCII,date格式的类型就是:

'01-Jan-01'      

   altersystemsetNLS_DATE_LANGUAGE=American      

   altersessionsetNLS_DATE_LANGUAGE=American      

   或者在to_date中写      

   selectto_char（to_date（'2002-08-26','yyyy-mm-dd'）,'day','NLS_DATE_LANGUAGE=American'）fromdual;      

   注意我这只是举了NLS_DATE_LANGUAGE，当然还有很多，      

   可查看      

   select*fromnls_session_parameters      

   select*fromV$NLS_PARAMETERS      

8.     

复制代码 代码如下:

  selectcount（*）      

  from（selectrownum-1rnum      

      fromall_objects      

      whererownum<=to_date（'2002-02-28','yyyy-mm-dd'）-to_date（'2002-      

      02-01','yyyy-mm-dd'）+1      

     ）      

  whereto_char（to_date（'2002-02-01','yyyy-mm-dd'）+rnum-1,'D'）      

       notin（'1','7'）     

  查找2002-02-28至2002-02-01间除星期一和七的天数      

  在前后分别调用DBMS_UTILITY.GET_TIME,让后将结果相减（得到的是1/100秒,而不是毫秒）.      

9.查找月份  

复制代码 代码如下:

   selectmonths_between（to_date（'01-31-1999','MM-DD-YYYY'）,to_date（'12-31-1998','MM-DD-YYYY'））"MONTHS"FROMDUAL;      

   1      

  selectmonths_between（to_date（'02-01-1999','MM-DD-YYYY'）,to_date（'12-31-1998','MM-DD-YYYY'））"MONTHS"FROMDUAL;      

   1.03225806451613

10.Next_day的用法     

复制代码 代码如下:

   Next_day（date,day）      

   Monday-Sunday,forformatcodeDAY      

   Mon-Sun,forformatcodeDY      

   1-7,forformatcodeD    

11      

  selectto_char（sysdate,'hh:

mi:

ss'）TIMEfromall_objects      

  注意：

第一条记录的TIME与最后一行是一样的      

  可以建立一个函数来处理这个问题     

复制代码 代码如下:

  createorreplacefunctionsys_datereturndateis      

  begin      

  returnsysdate;      

  end;      

  selectto_char（sys_date,'hh:

mi:

ss'）fromall_objects;  

12.获得小时数      

    extract（）找出日期或间隔值的字段值

复制代码 代码如下:

   SELECTEXTRACT（HOURFROMTIMESTAMP'2001-02-162:

38:

40'）fromoffer      

   SQL>selectsysdate,to_char（sysdate,'hh'）fromdual;      

   SYSDATETO_CHAR（SYSDATE,'HH'）      

   -----------------------------------------      

   2003-10-1319:

35:

2107      

   SQL>selectsysdate,to_char（sysdate,'hh24'）fromdual;      

   SYSDATETO_CHAR（SYSDATE,'HH24'）      

   -------------------------------------------      

   2003-10-1319:

35:

2119     

13.年月日的处理     

复制代码 代码如下:

  selectolder_date,      

      newer_date,      

      years,      

      months,      

      abs（      

       trunc（      

        newer_date-      

        add_months（older_date,years*12+months）      

       ）      

      ）days 

  from（select      

       trunc（months_between（newer_date,older_date）/12）YEARS,      

       mod（trunc（months_between（newer_date,older_date））,12）MONTHS,      

       newer_date,      

       older_date      

       from（ 

             selecthiredateolder_date,add_months（hiredate,rownum）+rownumnewer_date      

             fromemp 

            ）      

     ）     

14.处理月份天数不定的办法      

  selectto_char（add_months（last_day（sysdate）+1,-2）,'yyyymmdd'）,last_day（sysdate）fromdual      

16.找出今年的天数      

  selectadd_months（trunc（sysdate,'year'）,12）-trunc（sysdate,'year'）fromdual      

  闰年的处理方法      

  to_char（last_day（to_date（'02'   ||:

year,'mmyyyy'））,'dd'）      

  如果是28就不是闰年      

17.yyyy与rrrr的区别     

复制代码 代码如下:

  'YYYY99TO_C      

  -----------      

  yyyy990099      

  rrrr991999      

  yyyy010001      

  rrrr012001     

18.不同时区的处理      

  selectto_char（NEW_TIME（sysdate,'GMT','EST'）,'dd/mm/yyyyhh:

mi:

ss'）,sysdate      

  fromdual;   

19.5秒钟一个间隔  

复制代码 代码如下:

  SelectTO_DATE（FLOOR（TO_CHAR（sysdate,'SSSSS'）/300）*300,'SSSSS'）,TO_CHAR（sysdate,'SSSSS'）      

  fromdual      

  2002-11-19:

55:

0035786      

  SSSSS表示5位秒数     

20.一年的第几天      

  selectTO_CHAR（SYSDATE,'DDD'）,sysdatefromdual

  3102002-11-610:

03:

51      

21.计算小时,分,秒,毫秒 

复制代码 代码如下:

    select      

    Days,      

    A,      

    TRUNC（A*24）Hours,      

    TRUNC（A*24*60-60*TRUNC（A*24））Minutes,      

    TRUNC（A*24*60*60-60*TRUNC（A*24*60））Seconds,      

    TRUNC（A*24*60*60*100-100*TRUNC（A*24*60*60））mSeconds      

   from      

   （      

    select      

    trunc（sysdate）Days,      

    sysdate-trunc（sysdate）A      

    fromdual      

  ）      

  select*fromtabname      

  orderbydecode（mode,'FIFO',1,-1）*to_char（rq,'yyyymmddhh24miss'）;      

  //      

  floor（（date2-date1）/365）作为年      

  floor（（date2-date1,365）/30）作为月      

  d（mod（date2-date1,365）,30）作为日.

23.next_day函数     返回下个星期的日期,day为1-7或星期日-星期六,1表示星期日

  next_day（sysdate,6）是从当前开始下一个星期五。

后面的数字是从星期日开始算起。

  1234567      

  日一二三四五六    

  --------------------------------------------------------------- 

  select   （sysdate-to_date（'2003-12-0312:

55:

45','yyyy-mm-ddhh24:

mi:

ss'））*24*60*60fromddual

  日期返回的是天然后转换为ss

24,round[舍入到最接近的日期]（day:

舍入到最接近的星期日）

  selectsysdateS1,

  round（sysdate）S2,

  round（sysdate,'year'）YEAR,

  round（sysdate,'month'）MONTH,

  round（sysdate,'day'）DAYfromdual

25,trunc[截断到最接近的日期,单位为天],返回的是日期类型

  selectsysdateS1,                     

    trunc（sysdate）S2,                //返回当前日期,无时分秒

    trunc（sysdate,'year'）YEAR,       //返回当前年的1月1日,无时分秒

    trunc（sysdate,'month'）MONTH,    //返回当前月的1日,无时分秒

    trunc（sysdate,'day'）DAY          //返回当前星期的星期天,无时分秒

  fromdual

26,返回日期列表中最晚日期

  selectgreatest（'01-1月-04','04-1月-04','10-2月-04'）fromdual

27.计算时间差

    注:

oracle时间差是以天数为单位,所以换算成年月,日

     selectfloor（to_number（sysdate-to_date（'2007-11-0215:

55:

03','yyyy-mm-ddhh24:

mi:

ss'））/365）asspanYearsfromdual       //时间差-年

     selectceil（moths_between（sysdate-to_date（'2007-11-0215:

55:

03','yyyy-mm-ddhh24:

mi:

ss'）））asspanMonthsfromdual       //时间差-月

     selectfloor（to_number（sysdate-to_date（'2007-11-0215:

55:

03','yyyy-mm-ddhh24:

mi:

ss'）））asspanDaysfromdual            //时间差-天

     selectfloor（to_number（sysdate-to_date（'2007-11-0215:

55:

03','yyyy-mm-ddhh24:

mi:

ss'））*24）asspanHoursfromdual        //时间差-时

     selectfloor（to_number（sysdate-to_date（'2007-11-0215: