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;