感于總有些網(wǎng)友提出一些非常基礎(chǔ)的問題，比如有沒有實現(xiàn)某某功能的函數(shù)啊，某某函數(shù)是做什么用的啊，格式是什么等等，同時也感受到自己對oracle函數(shù)認識的不足，于是集中月余時間專注于oracle函數(shù)，小有心得不敢私藏，發(fā)之與諸公共享。

本文并不準備介紹全部的oracle函數(shù)，當前情勢下，俺也還沒這個時間，需要學習的東西太多了，要把多數(shù)時間花在學習經(jīng)常能用上的技術(shù)方面:)，所以如果是準備深入了解所有oracle函數(shù)的朋友，還是去關(guān)注：Oracle SQL Reference官方文檔更靠譜一些。

本文更多將會介紹三思在日常中經(jīng)常會用到的，或者雖然很少用到，但是感覺挺有意思的一些函數(shù)。分二類介紹，分別是：

著名函數(shù)篇-經(jīng)常用到的函數(shù)

非著名函數(shù)篇-即雖然很少用到，但某些情況下卻很實用

注：N表示數(shù)字型，C表示字符型，D表示日期型，[]表示內(nèi)中參數(shù)可被忽略，fmt表示格式。

單值函數(shù)在查詢中返回單個值，可被應用到select，where子句，start with以及connect by 子句和having子句。

(一).數(shù)值型函數(shù)(Number Functions)

數(shù)值型函數(shù)輸入數(shù)字型參數(shù)并返回數(shù)值型的值。多數(shù)該類函數(shù)的返回值支持38位小數(shù)點，諸如：COS, COSH, EXP, LN, LOG, SIN, SINH, SQRT, TAN, and TANH 支持36位小數(shù)點。ACOS, ASIN, ATAN, and ATAN2支持30位小數(shù)點。

1、MOD(n1,n2) 返回n1除n2的余數(shù)，如果n2=0則返回n1的值。

例如：SELECT MOD(24,5) FROM DUAL;

2、ROUND(n1[,n2]) 返回四舍五入小數(shù)點右邊n2位后n1的值，n2缺省值為0，如果n2為負數(shù)就舍入到小數(shù)點左邊相應的位上(雖然oracle documents上提到n2的值必須為整數(shù)，事實上執(zhí)行時此處的判斷并不嚴謹，即使n2為非整數(shù)，它也會自動將n2取整后做處理，但是我文檔中其它提到必須為整的地方需要特別注意，如果不為整執(zhí)行時會報錯的)。

例如：SELECT ROUND(23.56),ROUND(23.56,1),ROUND(23.56,-1) FROM DUAL;

3、TRUNC(n1[,n2] 返回截尾到n2位小數(shù)的n1的值，n2缺省設(shè)置為0，當n2為缺省設(shè)置時會將n1截尾為整數(shù)，如果n2為負值，就截尾在小數(shù)點左邊相應的位上。

例如：SELECT TRUNC(23.56),TRUNC(23.56,1),TRUNC(23.56,-1) FROM DUAL;

(二).字符型函數(shù)返回字符值(Character Functions Returning Character Values)

該類函數(shù)返回與輸入類型相同的類型。

? 返回的CHAR類型值長度不超過2000字節(jié);

? 返回的VCHAR2類型值長度不超過4000字節(jié);

如果上述應返回的字符長度超出，oracle并不會報錯而是直接截斷至最大可支持長度返回。

? 返回的CLOB類型值長度不超過4G;

對于CLOB類型的函數(shù)，如果返回值長度超出，oracle不會返回任何錯誤而是直接拋出錯誤。

1、LOWER(c) 將指定字符串內(nèi)字符變?yōu)樾懀С諧HAR,VARCHAR2,NCHAR,NVARCHAR2,CLOB,NCLOB類型

例如：SELECT LOWER('WhaT is tHis') FROM DUAL;

2、UPPER(c) 將指定字符串內(nèi)字符變?yōu)榇髮懀С諧HAR,VARCHAR2,NCHAR,NVARCHAR2,CLOB,NCLOB類型

例如：SELECT UPPER('WhaT is tHis') FROM DUAL;

　3、LPAD(c1,n[,c2]) 返回指定長度=n的字符串，需要注意的有幾點：

? 如果n

? 如果n>c1.length and c2 is null，以空格從左向右補充字符長度至n并返回;

? 如果n>c1.length and c2 is not null，以指定字符c2從左向右補充c1長度至n并返回;

例如：SELECT LPAD('WhaT is tHis',5),LPAD('WhaT is tHis',25),LPAD('WhaT is tHis',25,'-') FROM DUAL;

最后大家再猜一猜，如果n<0，結(jié)果會怎么樣

4、RPAD(c1,n[,c2]) 返回指定長度=n的字符串，基本與上同，不過補充字符是從右向左方向正好與上相反;

例如：SELECT RPAD('WhaT is tHis',5),RPAD('WhaT is tHis',25),RPAD('WhaT is tHis',25,'-') FROM DUAL;

5、TRIM([[LEADING||TRAILING||BOTH] c2 FROM] c1) 哈哈，被俺無敵的形容方式搞暈頭了吧，這個地方還是看圖更明了一些。

看起來很復雜，理解起來很簡單：

? 如果沒有指定任何參數(shù)則oracle去除c1頭尾空格

例如：SELECT TRIM(' WhaT is tHis ') FROM DUAL;

? 如果指定了c2參數(shù)，則oracle去掉c1頭尾c2(這個建議細致測試，有多種不同情形的喲)

例如：SELECT TRIM('W' FROM 'WhaT is tHis w W') FROM DUAL;

? 如果指定了leading參數(shù)則會去掉c1頭部c2

例如：SELECT TRIM(leading 'W' FROM 'WhaT is tHis w W') FROM DUAL;

? 如果指定了trailing參數(shù)則會去掉c1尾部c2

例如：SELECT TRIM(trailing 'W' FROM 'WhaT is tHis w W') FROM DUAL;

? 如果指定了both參數(shù)則會去掉c1頭尾c2(跟不指定有區(qū)別嗎?沒區(qū)別!)

例如：SELECT TRIM(both 'W' FROM 'WhaT is tHis w W') FROM DUAL;

注意：c2長度=1

6、LTRIM(c1[,c2]) 千萬表以為與上面那個長的像，功能也與上面的類似，本函數(shù)是從字符串c1左側(cè)截取掉與指定字符串c2相同的字符并返回。如果c2為空則默認截取空格。

例如：SELECT LTRIM('WWhhhhhaT is tHis w W','Wh') FROM DUAL;

7、RTRIM(c1,c2)與上同，不過方向相反

例如：SELECT RTRIM('WWhhhhhaT is tHis w W','W w') FROM DUAL;

8、REPLACE(c1,c2[,c3]) 將c1字符串中的c2替換為c3，如果c3為空，則從c1中刪除所有c2。

例如：SELECT REPLACE('WWhhhhhaT is tHis w W','W','-') FROM DUAL;

9、SOUNDEX(c) 神奇的函數(shù)啊，該函數(shù)返回字符串參數(shù)的語音表示形式，對于比較一些讀音相同，但是拼寫不同的單詞非常有用。計算語音的算法如下：

? 保留字符串首字母，但刪除a、e、h、i、o、w、y。

? 將下表中的數(shù)字賦給相對應的字母：

1：b、f、p、v

2：c、g、k、q、s、x、z

3：d、t

4：l

5：m、n

6：R

? 如果字符串中存在擁有相同數(shù)字的2個以上(包含2個)的字母在一起(例如b和f)，或者只有h或w，則刪除其他的，只保留1個;

? 只返回前4個字節(jié)，不夠用0填充

例如：SELECT SOUNDEX('dog'),soundex('boy') FROM DUAL;

10、SUBSTR(c1,n1[,n2]) 截取指定長度的字符串。稍不注意就可能充滿了陷阱的函數(shù)。

n1=開始長度;

n2=截取的字符串長度，如果為空，默認截取到字符串結(jié)尾;

? 如果n1=0 then n1=1

? 如果n1>0，則oracle從左向右確認起始位置截取

例如：SELECT SUBSTR('What is this',5,3) FROM DUAL;

? 如果n1<0，則oracle從右向左數(shù)確認起始位置

例如：SELECT SUBSTR('What is this',-5,3) FROM DUAL;

? 如果n1>c1.length則返回空

例如：SELECT SUBSTR('What is this',50,3) FROM DUAL;

然后再請你猜猜，如果n2<1，會如何返回值呢

11、TRANSLATE(c1,c2,c3) 就功能而言，此函數(shù)與replace有些相似。但需要注意的一點是，translate是絕對匹配替換，這點與replace函數(shù)具有非常大區(qū)別。什么是絕對匹配替換呢?簡單的說，是將字符串c1中按一定的格式c2替換為c3。如果文字形容仍然無法理解，我們通過幾具實例來說明：

例如：

SELECT TRANSLATE('What is this','','-') FROM DUAL;

SELECT TRANSLATE('What is this','-','') FROM DUAL;

結(jié)果都是空。來試試這個：

SELECT TRANSLATE('What is this',' ',' ') FROM DUAL;

再來看這個：

SELECT TRANSLATE('What is this','ait','-*') FROM DUAL;

是否明白了點呢?Replace函數(shù)理解比較簡單，它是將字符串中指定字符替換成其它字符，它的字符必須是連續(xù)的。而translate中，則是指定字符串c1中出現(xiàn)的c2，將c2中各個字符替換成c3中位置順序與其相同的c3中的字符。明白了?Replace是替換，而translate則像是過濾。

　(三).字符型函數(shù)返回數(shù)字值(Character Functions Returning Number Values)

本類函數(shù)支持所有的數(shù)據(jù)類型

1、INSTR(c1,c2[,n1[,n2]]) 返回c2在c1中位置

? c1:原字符串

? c2:要尋找的字符串

? n1:查詢起始位置，正值表示從左到右，負值表示從右到左 (大小表示位置,比如3表示左面第3處開始,-3表示右面第3處開始)。黑黑，如果為0的話，則返回的也是0

? n2:第幾個匹配項。大于0

例如：SELECT INSTR('abcdefg','e',-3) FROM DUAL;

2、LENGTH(c) 返回指定字符串的長度。如果

例如：SELECT LENGTH('A123中') FROM DUAL;

猜猜SELECT LENGTH('') FROM DUAL;的返回值是什么

(四).日期函數(shù)(Datetime Functions)

本類函數(shù)中，除months_between返回數(shù)值外，其它都將返回日期。

1、ADD_MONTHS() 返回指定日期月份+n之后的值，n可以為任何整數(shù)。

例如：SELECT ADD_MONTHS(sysdate,12),ADD_MONTHS(sysdate,-12) FROM DUAL;

2、CURRENT_DATE 返回當前session所在時區(qū)的默認時間

例如：

SQL> alter session set nls_date_format = 'mm-dd-yyyy' ;

SQL> select current_date from dual;

3、SYSDATE 功能與上相同，返回當前session所在時區(qū)的默認時間。但是需要注意的一點是，如果同時使用sysdate與current_date獲得的時間不一定相同，某些情況下current_date會比sysdate快一秒。經(jīng)過與xyf_tck(兄臺的大作ORACLE的工作機制寫的很好，深入淺出)的短暫交流，我們認為current_date是將current_timestamp中毫秒四舍五入后的返回，雖然沒有找到文檔支持，但是想來應該八九不離十。同時，僅是某些情況下會有一秒的誤差，一般情況下并不會對你的操作造成影響，所以了解即可。

例如：SELECT SYSDATE,CURRENT_DATE FROM DUAL;

4、LAST_DAY(d) 返回指定時間所在月的最后一天

例如：SELECT last_day(SYSDATE) FROM DUAL;

5、NEXT_DAY(d,n) 返回指定日期后第一個n的日期，n為一周中的某一天。但是，需要注意的是n如果為字符的話，它的星期形式需要與當前session默認時區(qū)中的星期形式相同。

例如：三思用的中文nt，nls_language值為SIMPLIFIED CHINESE

SELECT NEXT_DAY(SYSDATE,5) FROM DUAL;

SELECT NEXT_DAY(SYSDATE,'星期四') FROM DUAL;

兩種方式都可以取到正確的返回，但是：

SELECT NEXT_DAY(SYSDATE,'Thursday') FROM DUAL;

則會執(zhí)行出錯，提供你說周中的日無效，就是這個原因了。

6、MONTHS_BETWEEN(d1,d2) 返回d1與d2間的月份差，視d1,d2的值大小，結(jié)果可正可負，當然也有可能為0

例如：

SELECT months_between(SYSDATE, sysdate),

months_between(SYSDATE, add_months(sysdate, -1)),

months_between(SYSDATE, add_months(sysdate, 1))

FROM DUAL;

7、ROUND(d[,fmt]) 前面講數(shù)值型函數(shù)的時候介紹過ROUND，此處與上功能基本相似，不過此處操作的是日期。如果不指定fmt參數(shù)，則默認返回距離指定日期最近的日期。

例如：SELECT ROUND(SYSDATE,'HH24') FROM DUAL;

8、TRUNC(d[,fmt]) 與前面介紹的數(shù)值型TRUNC原理相同，不過此處也是操作的日期型。

例如：SELECT TRUNC(SYSDATE,'HH24') FROM DUAL;

(五).轉(zhuǎn)換函數(shù)(Conversion Functions)

轉(zhuǎn)換函數(shù)將指定字符從一種類型轉(zhuǎn)換為另一種，通常這類函數(shù)遵循如下慣例：函數(shù)名稱后面跟著待轉(zhuǎn)換類型以及輸出類型。

1、TO_CHAR() 本函數(shù)又可以分三小類，分別是

? 轉(zhuǎn)換字符->字符TO_CHAR(c)：將nchar,nvarchar2,clob,nclob類型轉(zhuǎn)換為char類型;

例如：SELECT TO_CHAR('AABBCC') FROM DUAL;

? 轉(zhuǎn)換時間->字符TO_CHAR(d[,fmt])：將指定的時間(data,timestamp,timestamp with time zone)按照指定格式轉(zhuǎn)換為varchar2類型;

例如：SELECT TO_CHAR(sysdate,'yyyy-mm-dd hh24:mi:ss') FROM DUAL;

? 轉(zhuǎn)換數(shù)值->字符TO_CHAR(n[,fmt])：將指定數(shù)值n按照指定格式fmt轉(zhuǎn)換為varchar2類型并返回;

例如：SELECT TO_CHAR(-100, 'L99G999D99MI') FROM DUAL;

2、TO_DATE(c[,fmt[,nls]]) 將char,nchar,varchar2,nvarchar2轉(zhuǎn)換為日期類型，如果fmt參數(shù)不為空，則按照fmt中指定格式進行轉(zhuǎn)換。注意這里的fmt參數(shù)。如果ftm為'J'則表示按照公元制(Julian day)轉(zhuǎn)換，c則必須為大于0并小于5373484的正整數(shù)。

例如：

SELECT TO_DATE(2454336, 'J') FROM DUAL;

SELECT TO_DATE('2007-8-23 23:25:00', 'yyyy-mm-dd hh24:mi:ss') FROM DUAL;

為什么公元制的話，c的值必須不大于5373484呢?因為Oracle的DATE類型的取值范圍是公元前4712年1月1日至公元9999年12月31日。看看下面這個語句：

SELECT TO_CHAR(TO_DATE('9999-12-31','yyyy-mm-dd'),'j') FROM DUAL;

3、TO_NUMBER(c[,fmt[,nls]]) 將char,nchar,varchar2,nvarchar2型字串按照fmt中指定格式轉(zhuǎn)換為數(shù)值類型并返回。

例如：SELECT TO_NUMBER('-100.00', '9G999D99') FROM DUAL;

(六).其它輔助函數(shù)(Miscellaneous Single-Row Functions)

1、DECODE(exp,s1,r1,s2,r2..s,r[,def]) 可以把它理解成一個增強型的if else，只不過它并不通過多行語句，而是在一個函數(shù)內(nèi)實現(xiàn)if else的功能。

exp做為初始參數(shù)。s做為對比值，相同則返回r，如果s有多個，則持續(xù)遍歷所有s，直到某個條件為真為止，否則返回默認值def(如果指定了的話)，如果沒有默認值，并且前面的對比也都沒有為真，則返回空。

毫無疑問，decode是個非常重要的函數(shù)，在實現(xiàn)行轉(zhuǎn)列等功能時都會用到，需要牢記和熟練使用。

例如：select decode('a2','a1','true1','a2','true2','default') from dual;

2、GREATEST(n1,n2,...n) 返回序列中的最大值

例如：SELECT GREATEST(15,5,75,8) "Greatest" FROM DUAL;

3、LEAST(n1,n2....n) 返回序列中的最小值

例如：SELECT LEAST(15,5,75,8) LEAST FROM DUAL;

4、NULLIF(c1,c2)

Nullif也是個很有意思的函數(shù)。邏輯等價于：CASE WHEN c1 = c2 THEN NULL ELSE c1 END

例如：SELECT NULLIF('a','b'),NULLIF('a','a') FROM DUAL;

5、NVL(c1,c2) 邏輯等價于IF c1 is null THEN c2 ELSE c1 END。c1,c2可以是任何類型。如果兩者類型不同，則oracle會自動將c2轉(zhuǎn)換為c1的類型。

例如：SELECT NVL(null, '12') FROM DUAL;

6、NVL2(c1,c2,c3) 大家可能都用到nvl，但你用過nvl2嗎?如果c1非空則返回c2,如果c1為空則返回c3

例如：select nvl2('a', 'b', 'c') isNull,nvl2(null, 'b', 'c') isNotNull from dual;

7、SYS_CONNECT_BY_PATH(col,c) 該函數(shù)只能應用于樹狀查詢。返回通過c1連接的從根到節(jié)點的路徑。該函數(shù)必須與connect by 子句共同使用。

例如：

create;table;tmp3( rootcol;varchar2(10), nodecol;varchar2(10) ); insert;into;tmp3;values;('','a001'); insert;into;tmp3;values;('','b001'); insert;into;tmp3;values;('a001','a002'); insert;into;tmp3;values;('a002','a004'); insert;into;tmp3;values;('a001','a003'); insert;into;tmp3;values;('a003','a005'); insert;into;tmp3;values;('a005','a008'); insert;into;tmp3;values;('b001','b003'); insert;into;tmp3;values;('b003','b005'); select;lpad(';',;level*10,'=');||'>'||;sys_connect_by_path(nodecol,'/') from;tmp3 start;with;rootcol;=;'a001' connect;by;prior;nodecol;=rootcol; 8、SYS_CONTEXT(c1,c2[,n]) 將指定命名空間c1的指定參數(shù)c2的值按照指定長度n截取后返回。

Oracle9i提供內(nèi)置了一個命名空間USERENV，描述了當前session的各項信息，其擁有下列參數(shù)：

? CURRENT_SCHEMA:當前模式名;

? CURRENT_USER:當前用戶;

? IP_ADDRESS:當前客戶端IP地址;

? OS_USER:當前客戶端操作系統(tǒng)用戶;

等等數(shù)十項，更詳細的參數(shù)列還請大家直接參考Oracle Online Documents

例如：SELECT SYS_CONTEXT('USERENV', 'SESSION_USER') FROM DUAL;

注：N表示數(shù)字型，C表示字符型，D表示日期型，[]表示內(nèi)中參數(shù)可被忽略，fmt表示格式。

單值函數(shù)在查詢中返回單個值，可被應用到select，where子句，start with以及connect by 子句和having子句。

(一).數(shù)值型函數(shù)(Number Functions)

數(shù)值型函數(shù)輸入數(shù)字型參數(shù)并返回數(shù)值型的值。多數(shù)該類函數(shù)的返回值支持38位小數(shù)點，諸如：COS, COSH, EXP, LN, LOG, SIN, SINH, SQRT, TAN, and TANH 支持36位小數(shù)點。ACOS, ASIN, ATAN, and ATAN2支持30位小數(shù)點。

1、ABS(n) 返回數(shù)字的絕對值

例如：SELECT ABS(-1000000.01) FROM DUAL;

2、COS(n) 返回n的余弦值

例如：SELECT COS(-2) FROM DUAL;

3、ACOS(n) 反余弦函數(shù)，n between -1 and 1，返回值between 0 and pi。

例如：SELECT ACOS(0.9) FROM DUAL;

4、BITAND(n1,n2) 位與運算，這個太有意思了，雖然沒想到可能用到哪里，詳細說明一下：

假設(shè)3,9做位與運算，3的二進制形式為：0011，9的二進制形式為：1001，則結(jié)果是0001，轉(zhuǎn)換成10進制數(shù)為1。

例如：SELECT BITAND(3,9) FROM DUAL;

5、CEIL(n) 返回大于或等于n的最小的整數(shù)值

例如：SELECT ceil(18.2) FROM DUAL;

考你一下，猜猜ceil(-18.2)的值會是什么呢？

6、FLOOR(n) 返回小于等于n的最大整數(shù)值

例如：SELECT FLOOR(2.2) FROM DUAL;

再猜猜floor(-2.2)的值會是什么呢

7、BIN_TO_NUM(n1,n2,....n) 二進制轉(zhuǎn)向十進制

例如：SELECT BIN_TO_NUM(1),BIN_TO_NUM(1,0),BIN_TO_NUM(1,1) FROM DUAL;

8、SIN(n) 返回n的正玄值,n為弧度。

例如：SELECT SIN(10) FROM DUAL;

9、SINH(n) 返回n的雙曲正玄值,n為弧度。

例如：SELECT SINH(10) FROM DUAL;

10、ASIN(n) 反正玄函數(shù)，n between -1 and 1，返回值between pi/2 and -pi/2。

例如：SELECT ASIN(0.8) FROM DUAL;

11、TAN(n) 返回n的正切值,n為弧度

例如：SELECT TAN(0.8) FROM DUAL;

12、TANH(n) 返回n的雙曲正切值,n為弧度

例如：SELECT TANH(0.8) FROM DUAL;

13、ATAN(n) 反正切函數(shù)，n表示弧度，返回值between pi/2 and -pi/2。

例如：SELECT ATAN(-444444.9999999) FROM DUAL;

14、EXP(n) 返回e的n次冪，e = 2.71828183 ...

例如：SELECT EXP(3) FROM DUAL;

15、LN(n) 返回n的自然對數(shù)，n>0

例如：SELECT LN(0.9) FROM DUAL;

16、LOG(n1,n2) 返回以n1為底n2的對數(shù)，n1 >0 and not 1 ，n2>0

例如：SELECT LOG(1.1,2.2) FROM DUAL;

17、POWER(n1,n2) 返回n1的n2次方。n1,n2可以為任意數(shù)值，不過如果m是負數(shù)，則n必須為整數(shù)

例如：SELECT POWER(2.2,2.2) FROM DUAL;

18、SIGN(n) 如果n<0返回-1,如果n>0返回1，如果n=0返回0.

例如：SELECT SIGN(14),SIGN(-14),SIGN(0) FROM DUAL;

19、SQRT(n) 返回n的平方根,n為弧度。n>=0

例如：SELECT SQRT(0.1) FROM DUAL;

(二).字符型函數(shù)返回字符值(Character Functions Returning Character Values)

該類函數(shù)返回與輸入類型相同的類型。

? 返回的CHAR類型值長度不超過2000字節(jié);

? 返回的VCHAR2類型值長度不超過4000字節(jié);

如果上述應返回的字符長度超出，oracle并不會報錯而是直接截斷至最大可支持長度返回。

? 返回的CLOB類型值長度不超過4G;

對于CLOB類型的函數(shù)，如果返回值長度超出，oracle不會返回任何錯誤而是直接拋出錯誤。

1、CHR(N[ USING NCHAR_CS]) 返回指定數(shù)值在當前字符集中對應的字符

例如：SELECT CHR(95) FROM DUAL;

2、CONCAT(c1,c2) 連接字符串，等同于||

例如：SELECT concat('aa','bb') FROM DUAL;

3、INITCAP(c) 將字符串中單詞的第一個字母轉(zhuǎn)換為大寫，其它則轉(zhuǎn)換為小寫

例如：SELECT INITCAP('whaT is this') FROM DUAL;

4、NLS_INITCAP(c) 返回指定字符串，并將字符串中第一個字母變大寫，其它字母變小寫

例如：SELECT NLS_INITCAP('中華miNZHu') FROM DUAL;

它還具有一個參數(shù)：Nlsparam用來指定排序規(guī)則，可以忽略，默認狀態(tài)該參數(shù)為當前session的排序規(guī)則。

(三).字符型函數(shù)返回數(shù)字值(Character Functions Returning Number Values)

本類函數(shù)支持所有的數(shù)據(jù)類型

1、ASCII(c) 與chr函數(shù)的用途剛剛相反，本函數(shù)返回指定字符在當前字符集下對應的數(shù)值。

例如：SELECT ASCII('_') FROM DUAL;

(四).日期函數(shù)(Datetime Functions)

本類函數(shù)中，除months_between返回數(shù)值外，其它都將返回日期。

1、CURRENT_TIMESTAMP([n]) 返回當前session所在時區(qū)的日期和時間。n表示毫秒級的精度，不大于6

例如：SELECT CURRENT_TIMESTAMP(3) FROM DUAL;

2、LOCALTIMESTAMP([n]) 與上同，返回當前session所在時區(qū)的日期和時間。n表示毫秒級的精度，不大于6

例如：SELECT LOCALTIMESTAMP(3) FROM DUAL;

3、SYSTIMESTAMP([n]) 與上同，返回當前數(shù)據(jù)庫所在時區(qū)的日期和時間，n表示毫秒級的精度，>0 and <6

例如：SELECT SYSTIMESTAMP(4) FROM DUAL;

4、DBTIMEZONE 返回數(shù)據(jù)庫的當前時區(qū)

例如：SELECT DBTIMEZONE FROM DUAL;

5、SESSIONTIMEZONE 返回當前session所在時區(qū)

例如：SELECT SESSIONTIMEZONE FROM DUAL;

6、EXTRACT(key from date) key=(year,month,day,hour,minute,second) 從指定時間提到指定日期列

例如：SELECT EXTRACT(year from sysdate) FROM DUAL;

7、TO_TIMESTAMP(c1[,fmt]) 將指定字符按指定格式轉(zhuǎn)換為timestamp格式。

例如：SELECT TO_TIMESTAMP('2007-8-22', 'YYYY-MM-DD HH:MI:SS') FROM DUAL;

(五).轉(zhuǎn)換函數(shù)(Conversion Functions)

轉(zhuǎn)換函數(shù)將指定字符從一種類型轉(zhuǎn)換為另一種，通常這類函數(shù)遵循如下慣例：函數(shù)名稱后面跟著待轉(zhuǎn)換類型以及輸出類型。

1、BIN_TO_NUM(n1,n2...n) 將一組位向量轉(zhuǎn)換為等價的十進制形式。

例如：SELECT BIN_TO_NUM(1,1,0) FROM DUAL;

2、CAST(c as newtype) 將指定字串轉(zhuǎn)換為指定類型，基本只對字符類型有效，比如char,number,date,rowid等。此類轉(zhuǎn)換有一個專門的表列明了哪種類型可以轉(zhuǎn)換為哪種類型，此處就不作酹述。

例如：SELECT CAST('1101' AS NUMBER(5)) FROM DUAL;

3、CHARTOROWID(c) 將字符串轉(zhuǎn)換為rowid類型

例如：SELECT CHARTOROWID('A003D1ABBEFAABSAA0') FROM DUAL;

4、ROWIDTOCHAR(rowid) 轉(zhuǎn)換rowid值為varchar2類型。返回串長度為18個字節(jié)。

例如：SELECT ROWIDTOCHAR(rowid) FROM DUAL;

5、TO_MULTI_BYTE(c) 將指定字符轉(zhuǎn)換為全角并返回char類型字串

例如：SELECT TO_MULTI_BYTE('ABC abc　中華') FROM DUAL;

6、TO_SINGLE_BYTE(c) 將指定字符轉(zhuǎn)換為半角并返回char類型字串

例如：SELECT TO_SINGLE_BYTE('ABC　abc中華') FROM DUAL;

(六).其它輔助函數(shù)(Miscellaneous Single-Row Functions)

1、COALESCE(n1,n2,....n) 返回序列中的第一個非空值

例如：SELECT COALESCE(null,5,6,null,9) FROM DUAL;

2、DUMP(exp[,fmt[,start[,length]]])

dump是個功能非常強悍的函數(shù)，對于深入了解oracle存儲的人而言相當有用。所以對于我們這些僅僅只是應用的人而言就不知道能將其應用于何處了。此處僅介紹用法，不對其功能做深入分析。

如上所示，dump擁有不少參數(shù)。其本質(zhì)是以指定格式，返回指定長度的exp的內(nèi)部表示形式的varchar2值。fmt含4種格式：8||10||16||17，分別表示8進制，10進制，16進制和單字符，默認為10進制。start參數(shù)表示開始位置，length表示以,分隔的字串數(shù)。

例如：SELECT DUMP('abcdefg',17,2,4) FROM DUAL;

3、EMPTY_BLOB,EMPTY_CLOB 這兩個函數(shù)都是返回空lob類型，通常被用于insert和update等語句以初始化lob列，或者將其置為空。EMPTY表示LOB已經(jīng)被初始化，只不過還沒有用來存儲數(shù)據(jù)。

4、NLS_CHARSET_NAME(n) 返回指定數(shù)值對應的字符集名稱。

例如：SELECT NLS_CHARSET_NAME(1) FROM DUAL;

5、NLS_CHARSET_ID(c) 返回指定字符對應的字符集id。

例如：SELECT NLS_CHARSET_ID('US7ASCII') FROM DUAL;

6、NLS_CHARSET_DECL_LEN(n1,n2) 返回一個NCHAR值的聲明寬度(以字符為單位).n1是該值以字節(jié)為單位的長度，n2是該值的字符集ID

例如：SELECT NLS_CHARSET_DECL_LEN(100, nls_charset_id('US7ASCII')) FROM DUAL;

7、SYS_EXTRACT_UTC(timestamp) 返回標準通用時間即格林威治時間。

例如：SELECT SYS_EXTRACT_UTC(current_timestamp) FROM DUAL;

8、SYS_TYPEID(object_type) 返回對象類型對應的id。

例如：這個這個，沒有建立過自定義對象，咋做示例?

9、UID 返回一個唯一標識當前數(shù)據(jù)庫用戶的整數(shù)。

例如：SELECT UID FROM DUAL;

10、USER 返回當前session用戶

例如：SELECT USER FROM DUAL;

11、USERENV(c) 該函數(shù)用來返回當前session的信息，據(jù)oracle文檔的說明，userenv是為了保持向下兼容的遺留函數(shù)。oracle公司推薦你使用sys_context函數(shù)調(diào)用USERENV命名空間來獲取相關(guān)信息，所以大家了解下就行了。

例如：SELECT USERENV('LANGUAGE') FROM DUAL;

12、VSIZE(c) 返回c的字節(jié)數(shù)。

例如：SELECT VSIZE('abc中華') FROM DUAL;

注：N表示數(shù)字型，C表示字符型，D表示日期型，[]表示內(nèi)中參數(shù)可被忽略，fmt表示格式。

單值函數(shù)在查詢中返回單個值，可被應用到select，where子句，start with以及connect by 子句和having子句。

(一).數(shù)值型函數(shù)(Number Functions)

數(shù)值型函數(shù)輸入數(shù)字型參數(shù)并返回數(shù)值型的值。多數(shù)該類函數(shù)的返回值支持38位小數(shù)點，諸如：COS, COSH, EXP, LN, LOG, SIN, SINH, SQRT, TAN, and TANH 支持36位小數(shù)點。ACOS, ASIN, ATAN, and ATAN2支持30位小數(shù)點。

1、ABS(n) 返回數(shù)字的絕對值

例如：SELECT ABS(-1000000.01) FROM DUAL;

2、COS(n) 返回n的余弦值

例如：SELECT COS(-2) FROM DUAL;

3、ACOS(n) 反余弦函數(shù)，n between -1 and 1，返回值between 0 and pi。

例如：SELECT ACOS(0.9) FROM DUAL;

4、BITAND(n1,n2) 位與運算，這個太有意思了，雖然沒想到可能用到哪里，詳細說明一下：

假設(shè)3,9做位與運算，3的二進制形式為：0011，9的二進制形式為：1001，則結(jié)果是0001，轉(zhuǎn)換成10進制數(shù)為1。

例如：SELECT BITAND(3,9) FROM DUAL;

5、CEIL(n) 返回大于或等于n的最小的整數(shù)值

例如：SELECT ceil(18.2) FROM DUAL;

考你一下，猜猜ceil(-18.2)的值會是什么呢

6、FLOOR(n) 返回小于等于n的最大整數(shù)值

例如：SELECT FLOOR(2.2) FROM DUAL;

再猜猜floor(-2.2)的值會是什么呢

7、BIN_TO_NUM(n1,n2,....n) 二進制轉(zhuǎn)向十進制

例如：SELECT BIN_TO_NUM(1),BIN_TO_NUM(1,0),BIN_TO_NUM(1,1) FROM DUAL;

8、SIN(n) 返回n的正玄值,n為弧度。

例如：SELECT SIN(10) FROM DUAL;

9、SINH(n) 返回n的雙曲正玄值,n為弧度。

例如：SELECT SINH(10) FROM DUAL;

10、ASIN(n) 反正玄函數(shù)，n between -1 and 1，返回值between pi/2 and -pi/2。

例如：SELECT ASIN(0.8) FROM DUAL;11、TAN(n) 返回n的正切值,n為弧度

例如：SELECT TAN(0.8) FROM DUAL;

12、TANH(n) 返回n的雙曲正切值,n為弧度

例如：SELECT TANH(0.8) FROM DUAL;

13、ATAN(n) 反正切函數(shù)，n表示弧度，返回值between pi/2 and -pi/2。

例如：SELECT ATAN(-444444.9999999) FROM DUAL;

14、EXP(n) 返回e的n次冪，e = 2.71828183 ...

例如：SELECT EXP(3) FROM DUAL;

15、LN(n) 返回n的自然對數(shù)，n>0

例如：SELECT LN(0.9) FROM DUAL;

16、LOG(n1,n2) 返回以n1為底n2的對數(shù)，n1 >0 and not 1 ，n2>0

例如：SELECT LOG(1.1,2.2) FROM DUAL;

17、POWER(n1,n2) 返回n1的n2次方。n1,n2可以為任意數(shù)值，不過如果m是負數(shù)，則n必須為整數(shù)

例如：SELECT POWER(2.2,2.2) FROM DUAL;

18、SIGN(n) 如果n<0返回-1,如果n>0返回1，如果n=0返回0.

例如：SELECT SIGN(14),SIGN(-14),SIGN(0) FROM DUAL;

19、SQRT(n) 返回n的平方根,n為弧度。n>=0

例如：SELECT SQRT(0.1) FROM DUAL;

(二).字符型函數(shù)返回字符值(Character Functions Returning Character Values)

該類函數(shù)返回與輸入類型相同的類型。

? 返回的CHAR類型值長度不超過2000字節(jié);

? 返回的VCHAR2類型值長度不超過4000字節(jié);

如果上述應返回的字符長度超出，oracle并不會報錯而是直接截斷至最大可支持長度返回。

? 返回的CLOB類型值長度不超過4G;

對于CLOB類型的函數(shù)，如果返回值長度超出，oracle不會返回任何錯誤而是直接拋出錯誤。

1、CHR(N[ USING NCHAR_CS]) 返回指定數(shù)值在當前字符集中對應的字符

例如：SELECT CHR(95) FROM DUAL;

2、CONCAT(c1,c2) 連接字符串，等同于||

例如：SELECT concat('aa','bb') FROM DUAL;3、INITCAP(c) 將字符串中單詞的第一個字母轉(zhuǎn)換為大寫，其它則轉(zhuǎn)換為小寫

例如：SELECT INITCAP('whaT is this') FROM DUAL;

4、NLS_INITCAP(c) 返回指定字符串，并將字符串中第一個字母變大寫，其它字母變小寫

例如：SELECT NLS_INITCAP('中華miNZHu') FROM DUAL;

它還具有一個參數(shù)：Nlsparam用來指定排序規(guī)則，可以忽略，默認狀態(tài)該參數(shù)為當前session的排序規(guī)則。

(三).字符型函數(shù)返回數(shù)字值(Character Functions Returning Number Values)

本類函數(shù)支持所有的數(shù)據(jù)類型

1、ASCII(c) 與chr函數(shù)的用途剛剛相反，本函數(shù)返回指定字符在當前字符集下對應的數(shù)值。

例如：SELECT ASCII('_') FROM DUAL;

(四).日期函數(shù)(Datetime Functions)

本類函數(shù)中，除months_between返回數(shù)值外，其它都將返回日期。

1、CURRENT_TIMESTAMP([n]) 返回當前session所在時區(qū)的日期和時間。n表示毫秒級的精度，不大于6

例如：SELECT CURRENT_TIMESTAMP(3) FROM DUAL;

2、LOCALTIMESTAMP([n]) 與上同，返回當前session所在時區(qū)的日期和時間。n表示毫秒級的精度，不大于6

例如：SELECT LOCALTIMESTAMP(3) FROM DUAL;

3、SYSTIMESTAMP([n]) 與上同，返回當前數(shù)據(jù)庫所在時區(qū)的日期和時間，n表示毫秒級的精度，>0 and <6

例如：SELECT SYSTIMESTAMP(4) FROM DUAL;

4、DBTIMEZONE 返回數(shù)據(jù)庫的當前時區(qū)

例如：SELECT DBTIMEZONE FROM DUAL;

5、SESSIONTIMEZONE 返回當前session所在時區(qū)

例如：SELECT SESSIONTIMEZONE FROM DUAL;

6、EXTRACT(key from date) key=(year,month,day,hour,minute,second) 從指定時間提到指定日期列

例如：SELECT EXTRACT(year from sysdate) FROM DUAL;

7、TO_TIMESTAMP(c1[,fmt]) 將指定字符按指定格式轉(zhuǎn)換為timestamp格式。

例如：SELECT TO_TIMESTAMP('2007-8-22', 'YYYY-MM-DD HH:MI:SS') FROM DUAL;

(五).轉(zhuǎn)換函數(shù)(Conversion Functions)

轉(zhuǎn)換函數(shù)將指定字符從一種類型轉(zhuǎn)換為另一種，通常這類函數(shù)遵循如下慣例：函數(shù)名稱后面跟著待轉(zhuǎn)換類型以及輸出類型。

1、BIN_TO_NUM(n1,n2...n) 將一組位向量轉(zhuǎn)換為等價的十進制形式。

例如：SELECT BIN_TO_NUM(1,1,0) FROM DUAL;

2、CAST(c as newtype) 將指定字串轉(zhuǎn)換為指定類型，基本只對字符類型有效，比如char,number,date,rowid等。此類轉(zhuǎn)換有一個專門的表列明了哪種類型可以轉(zhuǎn)換為哪種類型，此處就不作酹述。例如：SELECT CAST('1101' AS NUMBER(5)) FROM DUAL;

3、CHARTOROWID(c) 將字符串轉(zhuǎn)換為rowid類型

例如：SELECT CHARTOROWID('A003D1ABBEFAABSAA0') FROM DUAL;

4、ROWIDTOCHAR(rowid) 轉(zhuǎn)換rowid值為varchar2類型。返回串長度為18個字節(jié)。

例如：SELECT ROWIDTOCHAR(rowid) FROM DUAL;

5、TO_MULTI_BYTE(c) 將指定字符轉(zhuǎn)換為全角并返回char類型字串

例如：SELECT TO_MULTI_BYTE('ABC abc　中華') FROM DUAL;

6、TO_SINGLE_BYTE(c) 將指定字符轉(zhuǎn)換為半角并返回char類型字串

例如：SELECT TO_SINGLE_BYTE('ABC　abc中華') FROM DUAL;

　　(六).其它輔助函數(shù)(Miscellaneous Single-Row Functions)

1、COALESCE(n1,n2,....n) 返回序列中的第一個非空值

例如：SELECT COALESCE(null,5,6,null,9) FROM DUAL;

2、DUMP(exp[,fmt[,start[,length]]])

dump是個功能非常強悍的函數(shù)，對于深入了解oracle存儲的人而言相當有用。所以對于我們這些僅僅只是應用的人而言就不知道能將其應用于何處了。此處僅介紹用法，不對其功能做深入分析。

如上所示，dump擁有不少參數(shù)。其本質(zhì)是以指定格式，返回指定長度的exp的內(nèi)部表示形式的varchar2值。fmt含4種格式：8||10||16||17，分別表示8進制，10進制，16進制和單字符，默認為10進制。start參數(shù)表示開始位置，length表示以,分隔的字串數(shù)。

例如：SELECT DUMP('abcdefg',17,2,4) FROM DUAL;

3、EMPTY_BLOB,EMPTY_CLOB 這兩個函數(shù)都是返回空lob類型，通常被用于insert和update等語句以初始化lob列，或者將其置為空。EMPTY表示LOB已經(jīng)被初始化，只不過還沒有用來存儲數(shù)據(jù)。

4、NLS_CHARSET_NAME(n) 返回指定數(shù)值對應的字符集名稱。

例如：SELECT NLS_CHARSET_NAME(1) FROM DUAL;

5、NLS_CHARSET_ID(c) 返回指定字符對應的字符集id。

例如：SELECT NLS_CHARSET_ID('US7ASCII') FROM DUAL;

6、NLS_CHARSET_DECL_LEN(n1,n2) 返回一個NCHAR值的聲明寬度(以字符為單位).n1是該值以字節(jié)為單位的長度，n2是該值的字符集ID

例如：SELECT NLS_CHARSET_DECL_LEN(100, nls_charset_id('US7ASCII')) FROM DUAL;

7、SYS_EXTRACT_UTC(timestamp) 返回標準通用時間即格林威治時間。

例如：SELECT SYS_EXTRACT_UTC(current_timestamp) FROM DUAL;

8、SYS_TYPEID(object_type) 返回對象類型對應的id。

例如：這個這個，沒有建立過自定義對象，咋做示例?

9、UID 返回一個唯一標識當前數(shù)據(jù)庫用戶的整數(shù)。

例如：SELECT UID FROM DUAL;

10、USER 返回當前session用戶

例如：SELECT USER FROM DUAL;

11、USERENV(c) 該函數(shù)用來返回當前session的信息，據(jù)oracle文檔的說明，userenv是為了保持向下兼容的遺留函數(shù)。oracle公司推薦你使用sys_context函數(shù)調(diào)用USERENV命名空間來獲取相關(guān)信息，所以大家了解下就行了。

例如：SELECT USERENV('LANGUAGE') FROM DUAL;

12、VSIZE(c) 返回c的字節(jié)數(shù)。

例如：SELECT VSIZE('abc中華') FROM DUAL;