oracle中函数的用法,oracle中函数的用法一

round用法：

(1)：round( date, [ format ] )

round(to_date ('22-AUG-03'),'YEAR')

would

return '01-JAN-04'

round(to_date ('22-AUG-03'),'Q')

would

return '01-OCT-03'

round(to_date ('22-AUG-03'),'MONTH')

would

return '01-SEP-03'

round(to_date ('22-AUG-03'),'DDD')

would

return '22-AUG-03'

round(to_date ('22-AUG-03'),'DAY')

would

return '24-AUG-03'

(2)：round( number, [ decimal_places ])

round(123.236,2) return 123.24

round(123.456, 0) return 123

trunc用法：

(1)：trunc(for number)，直接截取，不作舍入

trunc(89.985，2)=89.98

trunc(89.985)=89

trunc(89.985，-1)=80

(2)：trunc ( date, [ format ] )

Unit

Valid format

parameters

Year

SYYYY, YYYY, YEAR, SYEAR, YYY,

YY, Y

ISO Year

IYYY, IY, I

Quarter

Q

Month

MONTH, MON, MM, RM

Week

WW

IW

IW

W

W

Day

DDD, DD, J

Start day of the week

DAY, DY, D

Hour

HH, HH12, HH24

Minute

MI

Oracle 8i, Oracle 9i, Oracle 10g, Oracle 11g

trunc(to_date('22-AUG-03'), 'YEAR')

would

return '01-JAN-03'

trunc(to_date('22-AUG-03'), 'Q')

would

return '01-JUL-03'

trunc(to_date('22-AUG-03'), 'MONTH')

would

return '01-AUG-03'

trunc(to_date('22-AUG-03'), 'DDD')

would

return '22-AUG-03'

trunc(to_date('22-AUG-03'), 'DAY')

would

return '17-AUG-03'

29.SIGN

取数字n的符号,大于0返回1,小于0返回-1,等于0返回0

SQL> select sign(123),sign(-100),sign(0) from dual;

SIGN(123) SIGN(-100) SIGN(0)

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

1 -1 0

30.SIN

返回一个数字的正弦值

SQL> select sin(1.57079) from dual;

SIN(1.57079)

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

1

31.SIGH

返回双曲正弦的值

SQL> select sin(20),sinh(20) from dual;

SIN(20) SINH(20)

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

.91294525 242582598

32.SQRT

返回数字n的根

SQL> select sqrt(64),sqrt(10) from dual;

SQRT(64) SQRT(10)

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

8 3.1622777

33.TAN

返回数字的正切值

SQL> select tan(20),tan(10) from dual;

TAN(20) TAN(10)

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

2.2371609 .64836083

34.TANH

返回数字n的双曲正切值

SQL> select tanh(20),tan(20) from dual;

TANH(20) TAN(20)

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

1 2.2371609

35.TRUNC

按照指定的精度截取一个数

SQL> select trunc(124.1666,-2) trunc1,trunc(124.16666,2) from

dual;

TRUNC1

TRUNC(124.16666,2)

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

100 124.16

36.ADD_MONTHS

增加或减去月份

SQL> select

to_char(add_months(to_date('199912','yyyymm'),2),'yyyymm') from

dual;

TO_CHA

------

200002

SQL> select

to_char(add_months(to_date('199912','yyyymm'),-2),'yyyymm') from

dual;

TO_CHA

------

199910

37.LAST_DAY

返回日期的最后一天

SQL> select

to_char(sysdate,'yyyy.mm.dd'),to_char((sysdate)+1,'yyyy.mm.dd')

from dual;

TO_CHAR(SY TO_CHAR((S

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

2004.05.09 2004.05.10

SQL> select last_day(sysdate) from dual;

LAST_DAY(S

----------

31-5月 -04

38.MONTHS_BETWEEN(date2,date1)

给出date2-date1的月份

SQL> select months_between('19-12月-1999','19-3月-1999')

mon_between from dual;

MON_BETWEEN

-----------

9

SQL>selectmonths_between(to_date('2000.05.20','yyyy.mm.dd'),to_date('2005.05.20','yyyy.mm.dd'))

mon_betw from dual;

MON_BETW

---------

-60

39.NEW_TIME(date,'this','that')

给出在this时区=other时区的日期和时间

SQL> select to_char(sysdate,'yyyy.mm.dd hh24:mi:ss')

bj_time,to_char(new_time

2 (sysdate,'PDT','GMT'),'yyyy.mm.dd hh24:mi:ss') los_angles from

dual;

BJ_TIME LOS_ANGLES

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

2004.05.09 11:05:32 2004.05.09 18:05:32

40.NEXT_DAY(date,'day')

给出日期date和星期x之后计算下一个星期的日期

SQL> select next_day('18-5月-2001','星期五') next_day from

dual;

NEXT_DAY

----------

25-5月 -01