oracle 函数（二）

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

 

41.SYSDATE
用来得到系统的当前日期
SQL> select to_char(sysdate,'dd-mm-yyyy day') from dual;

TO_CHAR(SYSDATE,'
-----------------
09-05-2004 星期日
trunc(date,fmt)按照给出的要求将日期截断,如果fmt='mi'表示保留分,截断秒
SQL> select to_char(trunc(sysdate,'hh'),'yyyy.mm.dd hh24:mi:ss') hh,
  2  to_char(trunc(sysdate,'mi'),'yyyy.mm.dd hh24:mi:ss') hhmm from dual;

HH                  HHMM
------------------- -------------------
2004.05.09 11:00:00 2004.05.09 11:17:00

 

42.CHARTOROWID
将字符数据类型转换为ROWID类型
SQL> select rowid,rowidtochar(rowid),ename from scott.emp;

ROWID              ROWIDTOCHAR(ROWID) ENAME
------------------ ------------------ ----------
AAAAfKAACAAAAEqAAA AAAAfKAACAAAAEqAAA SMITH
AAAAfKAACAAAAEqAAB AAAAfKAACAAAAEqAAB ALLEN
AAAAfKAACAAAAEqAAC AAAAfKAACAAAAEqAAC WARD
AAAAfKAACAAAAEqAAD AAAAfKAACAAAAEqAAD JONES

43.CONVERT(c,dset,sset)
将源字符串 sset从一个语言字符集转换到另一个目的dset字符集
SQL> select convert('strutz','we8hp','f7dec') "conversion" from dual;

conver
------
strutz

44.HEXTORAW
将一个十六进制构成的字符串转换为二进制

45.RAWTOHEXT
将一个二进制构成的字符串转换为十六进制