oracle函數

普通函數

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

數字類函數

正負值類：

ABS

絕對值，傳回值一定大于等于0

SIGN

傳回值隻有1（正數）、-1（負數）和0（零）

舍入類的函數

CEL,向上取整

ceil(1.06)=2,ceil1.9)=2

FLOOR,向下取整

floor(1.06)=1, floor(1.9)=1

ROUND,四舍五入

round(1.06)=1, round(1.9)=2. round(15.06,1)=15.1, round(15.06,-1)=20

TRUNC,截斷

trunc(1.06)=1, trunc(1.9)=1, trunc(15.16,1)=15.1, trunc(15.16,-1)=10

求餘類的函數

MOD,求餘

經典：mod(n2,n1)=n2-n1* FLOOR(n2/n1)

oracle: mod(n2, n1)=sign(n2*(abs(n2)-abs(n1)*FLOOR(abs(n2)/abs(n1)))

・n2為正數：mod(13,5)=3,mod(13.12,5)=3.12,mod(13.12,5.1)=2.92,mod(13.12,-5)=3.12

n2為負數：mod(-13,5)=-3,mod(-13.12,5.1)=-2.92

Oracle裡的mod求餘和傳統意義上的mod函數不一樣

REMAINDER,求餘2

・經典： remainder(n2.n1)=n2-n1* ROUND(n2/n1)

oracle remainder(n2, n1)=n2-n1*(case when ABS(2/n1)-05=TRUNC(ABS(n2/n1))and

mod(ROUND(AB$( 2/n1)). 2)=1 then ROUND(n2/n1)-SIGN(2/n1)else ROUND(n2/n1)end

n2為正數： remainder(13,5)=-2, remainder(113)=1

・n2為負數： remainder(-13,5)=2, remainder(-13.12,5.1)=2.18

通常我們使用MOD,而不使用 REMAINDER

幂指類函數

幕函數： POWER

power(2,10)=1024

指數函數：EXP

exp(1)=2.718281828459045

對數函數：LN、LOG

・In(2.718281828459045)=1

og(2.8)=3,log(2,1024)=10

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

日期類

取日期值的某一部分

TO CHARE函數

假定 sysdate為2013-04-1313:06:28

to_char(sysdate, yyyy)=2013, to_char(sysdate, ss) =28

EXTRACT函數

extract(MONTH from date 2013-01-312)=1

extract(SECOND from timestamp 2013-08-04 09:00:05)=5

YEAR, MONTH, DAY, HOUR, MINUTE, SECOND

計算日期加法

直接加上一個數字，表示在目前日期上增加的時長

date'2013-01-01'+30=date'2013-01-31

date'2013-01-01'+29+1/12=to_date('2013-01-3002yy-mm-dh24)

ADD MONTHS函數

add months(date2010-02-27,24)=date'2012-02-27

add months(date 2012-02-29, 24)=add months(date 2012-02-28, 24)

add months(date 2010-02-28, 24)=？

INTERVAL表示法

date 1900-01-01+interval' 113-2"year(3)to month=date 2013-03-01

date 2013-04-17"+ interval1 23'day to hour=to_date(2013-04-18 23, yyyy-mm-dd hh24)

NUMTOYMINTERVALINUMTODSINTERVAL

date 2013-01-28+ numtodsinterval31, DAY")=date 2013-02-28

date 2013-01-31+numtodsinterval(28, DAY)=Date'2013-02-28

date 2013-01-28"+numtoyminterval(1, month)=date 2013-02-28

date 2013-01-31+numtoyminterval(1, month)=?

計算日期內插補點

直接相減，得天數

date'2013-01-31'-to_date(2013-01-0112:00:00,yy-mm-dh24:miss)=29.5

MONTHS_ BETWEEN函數

months between(date 2013-01-31, to date( 2010-11-01 12, yyyy-mm-dd hh24)=26.9516129

SCOTTOLW. lw2> select 29.5/31 from dual

29.5/31

.951612903

日期舍入

TRUNC函數

trunc(to date( 12: 35: 35, hh24: i: s). MI)=to date(12: 35:00, hh24: i: s)

trunc(to date( 12: 35: 35, hh24: mi: s), HH)=to date(12: 00, hh24: mi)

ROUND函數

round(date'2013-04-17,MM)=date'2013-05-01

round(o date( 12:35: 25, hh24: mi: s, MI)=to date( 12: 35:00, hh24: mi: ss)

round( o date( 12: 35: 35 h24: m: s), M)=to date(12: 36:00, hh24: mi: s)

其他正常函數

ー NEXT DAY

ー LAST DAY

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

字元類單行函數

大小寫轉換

INITCAP

LOWER

UPPER

字元與ASCⅡ碼轉換

ASCII

CHR

字元串連接配接

CONCAT函數，同操作符

SCOTT@lwlw2> set heading off

SCOTT@lwlw2> select 'last'||'winner'||''from dual

lastwinner

SCOTT@lwlw2> select concat(concat('last','winner'), ")from dual

字元串填補

LPAD、RPAD函數

lpad(1234,95)='5551234'

lpad(1234,3,"5)='123'

lpad(1234,9,"56)='565651234'

lpad(1234,9,數)=' 數數1234' --漢字前有個空格

字元串修剪

LTRIM和 RTRIM函數

預設從左或右去掉空格

從左或右去掉第一個參數中的字元。直到該字元不等于第二個參數中的任意字元

Itrim('aabdwecab, ' abcd')='wecab'

rtrim('Today is a good day.  ')='Today is a good day.'

TRIM函數

預設去掉兩端的空格

從左或/和右邊去掉第二個參數指定的字元，僅一個

SCOTT@lwlw2> select trim('abcd' from 'aabdwecab')from dual;

第1行出現錯誤

ORA-30001:截取集僅能有一個字元

SCOTT@lw lw2> select trim('a'from'aabdwecaba') from dual;

bdwecab

取子串

SUBSTR函數

SCOTT@lwlw2> select substr(lastwinner@dataguru', 5)from dual;

winner@dataquru

SCOTT@lwlw2> select substr( lastwinner@dataguru' 5,6) from dual;

wInner

SCOTT@wlw2> select substr('lastwinner@dataguru',-5,6)from dual;

aguru

SCOTT@lwlw2> select substr('lastwinner@dataguru, -5, 3)from dual;

agu

查找替換

INSTR函數

SCOTT@lwlw2> select instr('lastwinner@dataguru', 'win' )from dual;

5

REPLACE函數

select replace('Today is a good day. ',' ') from dual;

select replace('Today is a good day. ',' ','##') from dual;

TRANSLATE函數

select translate('Today is a good day. ',' ','##') from dual;

select translate('(+0086-)010-68345678','0(+)-','0') from dual;

select translate('1last34winn00er97是劍2破53冰45山4的5作5者之4453一45','l0123456789','l') from dual;

通用類單行函數

GREATEST

可跟若幹參數(類型可為數字、字元、日期等)，傳回其中的最大值，若參數中有一個NUL

則傳回NUL

以第一個參數的類型為準對其他參數做可能的隐式類型轉換

注意不要和MAX搞混

select greatest(sysdate,date'2021-12-31',to_date('13:12','mi:hh')+interval '1' year) from dual;

2022/9/1 12:13:00

LEAST

基本同上，隻不過求的是最小值，并且不要和min搞混

轉換類單行函數

CAST

TO CHAR

TO DATE

TO NUMBER

TO TIMESTAMP

DECODE

decode(條件,值1,傳回值1,值2,傳回值2,…值n,傳回值n,預設值)

decode(字段或字段的運算，值1，值2，值3）

這個函數運作的結果是，當字段或字段的運算的值等于值1時，該函數傳回值2，否則傳回值3

NVL

nvl(a,b)    if(a==null)return b else return a

NVL2

nvl2(a,b,c) if (a==null)return c else return b:

COALESCE

傳回參數中第個非NULL的值。若參數都是NULL,則傳回NULL