Oracle函數列錶速查1

PL/SQL單行函數和組函數詳解

　　函數是一種有零個或多個參數並且有一個傳回值的程式。在SQL中Oracle內建了一系列函數，這些函數都可被稱為SQL或PL/SQL語句，函數主要分為兩大類：

　　單行函數

　　組函數

　　本文將討論如何利用單行函數以及使用規則。

　　SQL中的單行函數
SQL和PL/SQL中內建很多類型的函數，有字元、數字、日期、轉換、和混合型等多種函數用於處理單行資料，因此這些都可被統稱為單行函數。這些函數均可用於SELECT,WHERE、ORDER BY等子句中，例如下面的例子中就包含了TO_CHAR,UPPER,SOUNDEX等單行函數。

　　SELECT ename,TO_CHAR(hiredate,'day,DD-Mon-YYYY')FROM empWhere UPPER(ename) Like 'AL%'ORDER BY SOUNDEX(ename)

　　單行函數也可以在其他語句中使用，如update的SET子句，INSERT的VALUES子句，DELET的WHERE子句,認證考試特別注意在SELECT語句中使用這些函數，所以我們的注意力也集中在SELECT語句中。

　　NULL和單行函數

　　在如何理解NULL上開始是很困難的，就算是一個很有經驗的人依然對此感到困惑。NULL值表示一個未知資料或者一個空值，算術操作符的任何一個運算元為NULL值，結果均為提個NULL值,這個規則也適合很多函數，只有CONCAT,DECODE,DUMP,NVL,REPLACE在調用了NULL參數時能夠返回非NULL值。在這些中NVL函數時最重要的，因為他能直接處理NULL值，NVL有兩個參數：NVL(x1,x2),x1和x2都式運算式，當x1為null時返回X2,否則返回x1。

　　下面我們看看emp資料表它包含了薪水、獎金兩項，需要計算總的補償

　　column name emp_id salary bonuskey type pk nulls/unique nn,u nnfk table datatype number number numberlength 11.2 11.2

　　不是簡單的將薪水和獎金加起來就可以了，如果某一行是null值那麼結果就將是null，比如下面的例子：

　　update empset salary=(salary+bonus)*1.1

　　這個語句中，僱員的工資和獎金都將更新為一個新的值，但是如果沒有獎金，即 salary + null,那麼就會得出錯誤的結論，這個時候就要使用nvl函數來排除null值的影響。

　　所以正確的語句是：

　　update empset salary=(salary+nvl(bonus,0)*1.1

　　單行字串函數

　　單行字串函數用於操作字串資料，他們大多數有一個或多個參數，其中絕大多數返回字串

　　ASCII()

　　c1是一字串，返回c1第一個字母的ASCII碼，他的逆函數是CHR()

　　SELECT ASCII('A') BIG_A,ASCII('z') BIG_z FROM empBIG_A BIG_z65 122

　　CHR(＜i＞)[NCHAR_CS]

　　i是一個數字，函數返回十進位表示的字元

　　select CHR(65),CHR(122),CHR(223) FROM empCHR65 CHR122 CHR223A z B

　　CONCAT(,)

　　c1,c2均為字串，函數將c2串連到c1的後面，如果c1為null,將返回c2.如果c2為null,則返回c1，如果c1、c2都為null，則返回null。他和操作符||返回的結果相同

　　select concat('slobo ','Svoboda') username from dualusernameslobo Syoboda

　　INITCAP()

　　c1為一字串。函數將每個單詞的第一個字母大寫其它字母小寫返回。單詞由空格，控制字元，標點符號限制。

　　select INITCAP('veni,vedi,vici') Ceasar from dualCeasarVeni,Vedi,Vici

　　INSTR(,[,＜i＞[,]])

　　c1,c2均為字串，i,j為整數。函數返回c2在c1中第j次出現的位置，搜尋從c1的第i個字元開始。當沒有發現需要的字元時返回0,如果i為負數，那麼搜尋將從右至左進行，但是位置的計算還是從左至右，i和j的預設值為1.

　　select INSTR('Mississippi','i',3,3) from dualINSTR('MISSISSIPPI','I',3,3)11select INSTR('Mississippi','i',-2,3) from dualINSTR('MISSISSIPPI','I',3,3)2

　　INSTRB(,[,i[,j])

　　與INSTR（）函數一樣，只是他返回的是位元組，對於單位元組INSTRB()等於INSTR()
 POWER(,)

　　返回n1的n2次方

　　ROUND(,)

　　返回舍入小數點右邊n2位的n1的值，n2的預設值為0，這回將小數點最接近的整數，如果n2為負數就舍入到小數點左邊相應的位上，n2必須是整數。

　　select ROUND(12345,-2),ROUND(12345.54321,2) FROM dualROUND(12345,-2) ROUND(12345.54321,2)12300 12345.54

　　SIGN()

　　如果n為負數，返回-1,如果n為正數，返回1，如果n=0返回0.

　　SIN（)

　　返回n的正玄值,n為弧度。

　　SINH()

　　返回n的雙曲正玄值,n為弧度。

　　SQRT()

　　返回n的平方根,n為弧度

　　TAN（)

　　返回n的正切值,n為弧度

　　TANH()

　　返回n的雙曲正切值,n為弧度

　　TRUNC(,)

　　返回截尾到n2位小數的n1的值，n2預設設定為0，當n2為預設設定時會將n1截尾為整數，如果n2為負值，就截尾在小數點左邊相應的位上。

　　單行日期函數

　　單行日期函數操作DATA資料類型，絕大多數都有DATA資料類型的參數，絕大多數返回的也是DATA資料類型的值。

　　ADD_MONTHS(,＜i＞)

　　返回日期d加上i個月後的結果。i可以使任意整數。如果i是一個小數，那麼資料庫將隱式的他轉換成整數，將會截去小數點後面的部分。

　　LAST_DAY()

　　函數返回包含日期d的月份的最後一天

　　MONTHS_BETWEEN(,)

　　返回d1和d2之間月的數目,如果d1和d2的日的日期都相同，或者都使該月的最後一天，那麼將返回一個整數，否則會返回的結果將包含一個分數。

　　NEW_TIME(,,)

　　d1是一個日期資料類型，當時區tz1中的日期和時間是d時，返回時區tz2中的日期和時間。tz1和tz2時字串。

　　NEXT_DAY(,)

　　返回日期d後由dow給出的條件的第一天，dow使用當前會話中給出的語言指定了一周中的某一天，返回的時間分量與d的時間分量相同。

　　select NEXT_DAY('01-Jan-2000','Monday') "1st Monday",NEXT_DAY('01-Nov-2004','Tuesday')+7 "2nd Tuesday") from dual;1st Monday 2nd Tuesday03-Jan-2000 09-Nov-2004

　　ROUND([,])

　　將日期d按照fmt指定的格式舍入，fmt為字串。

　　SYADATE

　　函數沒有參數，返回當前日期和時間。

　　TRUNC([,])

　　返回由fmt指定的單位的日期d.

　　單行轉換函式

　　單行轉換函式用於操作多資料類型，在資料類型之間進行轉換。

　　CHARTORWID()

　　c 使一個字串，函數將c轉換為RWID資料類型。

　　SELECT test_id from test_case where rowid=CHARTORWID('AAAA0SAACAAAALiAAA')

　　CONVERT(,[,])

　　c尾字串，dset、sset是兩個字元集，函數將字串c由sset字元集轉換為dset字元集，sset的預設設定為資料庫的字元集。

　　HEXTORAW()

　　x為16進位的字串，函數將16進位的x轉換為RAW資料類型。

　　RAWTOHEX()

　　x是RAW資料類型字串，函數將RAW資料類轉換為16進位的資料類型。

　　ROWIDTOCHAR()

　　函數將ROWID資料類型轉換為CHAR資料類型。

　　TO_CHAR([[,)

　　x是一個data或number資料類型，函數將x轉換成fmt指定格式的char資料類型，如果x為日期nlsparm=NLS_DATE_LANGUAGE 控制返回的月份和日份所使用的語言。如果x為數字nlsparm=NLS_NUMERIC_CHARACTERS 用來指定小數位和千分位的分隔字元，以及貨幣符號。

　　NLS_NUMERIC_CHARACTERS ="dg", NLS_CURRENCY="string"

　　TO_DATE([,[,）

　　c表示字串，fmt表示一種特殊格式的字串。返回按照fmt格式顯示的c,nlsparm表示使用的語言。函數將字串c轉換成date資料類型。

　　TO_MULTI_BYTE()

　　c表示一個字串，函數將c的擔子截字元轉換成多位元組字元。