Oracle SQL 內置函數大全(2)

26.MOD(n1,n2) 返回一個n1除以n2的餘數SQL> select mod(10,3),mod(3,3),mod(2,3) from dual;

MOD(10,3) MOD(3,3) MOD(2,3)--------- --------- ---------1 0 2

27.POWER 返回n1的n2次方根SQL> select power(2,10),power(3,3) from dual;

POWER(2,10) POWER(3,3)----------- ----------1024 27

28.ROUND和TRUNC按照指定的精度進行舍入SQL> select round(55.5),round(-55.4),trunc(55.5),trunc(-55.5) from dual;

ROUND(55.5) ROUND(-55.4) TRUNC(55.5) TRUNC(-55.5)----------- ------------ ----------- ------------56 -55 55 -55

29.SIGN 取數字n的符號,大於0返回1,小於0返回-1,等於0返回0SQL> 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------200002SQL> 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.10SQL> 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-----------9SQL>selectmonths_between(to_date("2000.05.20","yyyy.mm.dd"),to_date("2005.05.20","yyyy.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_time2 (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 SMITHAAAAfKAACAAAAEqAAB AAAAfKAACAAAAEqAAB ALLENAAAAfKAACAAAAEqAAC AAAAfKAACAAAAEqAAC WARDAAAAfKAACAAAAEqAAD AAAAfKAACAAAAEqAAD JONES