Oracle常用函数1.docx

资源描述

Oracle常用函数1.docx

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

Oracle常用函数1.docx

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两位年显示值:

yyythreedigits三位年显示值:

007

yyyyfourdigits四位年显示值:

2007

Month:

mm number 两位月显示值:

mon abbreviated字符集表示显示值:

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

monthspelledout字符集表示显示值:

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

Day:

dd number 当月第几天显示值:

ddd number 当年第几天显示值:

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

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

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

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

ddspthspelledout,ordinaltwelfth

Hour:

hh twodigits12小时进制显示值:

hh24twodigits24小时进制显示值:

Minute:

mi twodigits60进制显示值:

Second:

ss twodigits60进制显示值:

其它

Q digit 季度显示值:

WW digit 当年第几周显示值:

W digit 当月第几周显示值:

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

00:

00-23:

59:

59....

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

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

复制代码代码如下:

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;

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

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:

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

复制代码代码如下:

select

Days,

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:

展开阅读全文