oracle常用函數)

DECODEDECODE( 1 1 2 2)

IF THEN RETURN()

 

IF THEN RETURN()

                                    ELSE RETURN()

NVLNVL(EXPR1,EXPR2)

EXPR1NULL,EXPR2,EXPR1.

Substr的格式：

substr('This is a test', 6, 2)          would return 'is'
substr('This is a test', 6)              would return 'is a test'

Round 函數 (四捨五入) ：

SELECT ROUND( number, [ decimal_places ] ) FROM DUAL
參數:
number : 欲處理之數值
decimal_places : 四捨五入 , 小數取幾位 ( 預設為 0 )

CONCAT

;

     select concat('029-','88888888')||'11'  from dual;

LENGTH

;

select name,length(name),addr,length(addr),sal,length(to_char(sal)) from gao.nchar_tst;

SUBSTR(string,start,count)

,

start,count select substr('13088888888',3,8) from dual;

CEIL

返回大於或等於給出數位最小整數

SQL> select ceil(3.1415927) from dual;

ADD_MONTHS

增加或減去月份

SQL> select to_char(add_months(to_date('199912','yyyymm'),2),'yyyymm') from dual;

LAST_DAY

返回日期的最後一天

 select to_char(sysdate,'yyyy.mm.dd'),to_char((sysdate)+1,'yyyy.mm.dd') from dual;

MONTHS_BETWEEN(date2,date1)

date2-date1

 select months_between('19-12-1999','19-3-1999') mon_between from dual;

NEXT_DAY(date,'day')

select next_day('18-5

SYSDATE

用來得到系統的當前日期

 select to_char(sysdate,'dd-mm-yyyy day') from dual;

TO_CHAR(date,'format')

 select to_char(sysdate,'yyyy/mm/dd hh24:mi:ss') from dual;

TO_DATE(string,'format')

TO_NUMBER

將給出的字元轉換為數字

 select to_number('1999') year from dual;