本文更多將會介紹三思在日常中經常會用到的，或者雖然很少用到，但是感覺挺有意思的一些函數。分二類介紹，分別是：
著名函數篇 －經常用到的函數
非著名函數篇－即雖然很少用到，但某些情況下卻很實用
注：N表示數字型，C表示字符型，D表示日期型，[]表示內中參數可被忽略，fmt表示格式。
單值函數在查詢中返回單個值，可被應用到select，where子句，start with以及connect by 子句和having子句。
(一).數值型函數(Number Functions)
數值型函數輸入數字型參數并返回數值型的值。多數該類函數的返回值支持38位小數點，諸如：COS, COSH, EXP, LN, LOG, SIN, SINH, SQRT, TAN, and TANH 支持36位小數點。ACOS, ASIN, ATAN, and ATAN2支持30位小數點。
1、MOD(n1,n2) 返回n1除n2的余數，如果n2=0則返回n1的值。
例如：SELECT MOD(24,5) FROM DUAL;
2、ROUND(n1[,n2]) 返回四舍五入小數點右邊n2位后n1的值，n2缺省值為0，如果n2為負數就舍入到小數點左邊相應的位上(雖然Oracle documents上提到n2的值必須為整數，事實上執行時此處的判斷并不嚴謹，即使n2為非整數，它也會自動將n2取整后做處理，但是我文檔中其它提到必須為整的地方需要特別注意，如果不為整執行時會報錯的)。
例如：SELECT ROUND(23.56),ROUND(23.56,1),ROUND(23.56,-1) FROM DUAL;
3、TRUNC(n1[,n2] 返回截尾到n2位小數的n1的值，n2缺省設置為0，當n2為缺省設置時會將n1截尾為整數，如果n2為負值，就截尾在小數點左邊相應的位上。
例如：SELECT TRUNC(23.56),TRUNC(23.56,1),TRUNC(23.56,-1) FROM DUAL;
(二).字符型函數返回字符值(Character Functions Returning Character Values)
該類函數返回與輸入類型相同的類型。
返回的CHAR類型值長度不超過2000字節；
返回的VCHAR2類型值長度不超過4000字節；
如果上述應返回的字符長度超出，oracle并不會報錯而是直接截斷至最大可支持長度返回。
 返回的CLOB類型值長度不超過4G；
對于CLOB類型的函數，如果返回值長度超出，oracle不會返回任何錯誤而是直接拋出錯誤。
1、LOWER(c) 將指定字符串內字符變為小寫，支持CHAR,VARCHAR2,NCHAR,NVARCHAR2,CLOB,NCLOB類型
例如：SELECT LOWER('WhaT is tHis') FROM DUAL;
2、UPPER(c) 將指定字符串內字符變為大寫，支持CHAR,VARCHAR2,NCHAR,NVARCHAR2,CLOB,NCLOB類型
例如：SELECT UPPER('WhaT is tHis') FROM DUAL;
3、LPAD(c1,n[,c2]) 返回指定長度=n的字符串，需要注意的有幾點：
 如果n<c1.length則從右到左截取指定長度返回；
 如果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，結果會怎么樣
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) 哈哈，被俺無敵的形容方式搞暈頭了吧，這個地方還是看圖更明了一些。
看起來很復雜，理解起來很簡單：
 如果沒有指定任何參數則oracle去除c1頭尾空格
例如：SELECT TRIM(' WhaT is tHis ') FROM DUAL;
 如果指定了c2參數，則oracle去掉c1頭尾c2(這個建議細致測試，有多種不同情形的喲)
例如：SELECT TRIM('W' FROM 'WhaT is tHis w W') FROM DUAL;
 如果指定了leading參數則會去掉c1頭部c2
例如：SELECT TRIM(leading 'W' FROM 'WhaT is tHis w W') FROM DUAL;
 如果指定了trailing參數則會去掉c1尾部c2
例如：SELECT TRIM(trailing 'W' FROM 'WhaT is tHis w W') FROM DUAL;
 如果指定了both參數則會去掉c1頭尾c2(跟不指定有區別嗎？沒區別！)
例如：SELECT TRIM(both 'W' FROM 'WhaT is tHis w W') FROM DUAL;
注意：c2長度=1
6、LTRIM(c1[,c2]) 千萬表以為與上面那個長的像，功能也與上面的類似，本函數是從字符串c1左側截取掉與指定字符串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) 神奇的函數啊，該函數返回字符串參數的語音表示形式，對于比較一些讀音相同，但是拼寫不同的單詞非常有用。計算語音的算法如下：
 保留字符串首字母，但刪除a、e、h、i、o、w、y。
 將下表中的數字賦給相對應的字母：
1：b、f、p、v
2：c、g、k、q、s、x、z
3：d、t
4：l
5：m、n
6：R
 如果字符串中存在擁有相同數字的2個以上（包含2個）的字母在一起（例如b和f），或者只有h或w，則刪除其他的，只保留1個；
 只返回前4個字節，不夠用0填充
例如：SELECT SOUNDEX('dog'),soundex('boy') FROM DUAL;
10、SUBSTR(c1,n1[,n2]) 截取指定長度的字符串。稍不注意就可能充滿了陷阱的函數。
n1=開始長度；
n2=截取的字符串長度，如果為空，默認截取到字符串結尾；
 如果n1=0 then n1=1
 如果n1>0，則oracle從左向右確認起始位置截取
例如：SELECT SUBSTR('What is this',5,3) FROM DUAL;
 如果n1<0，則oracle從右向左數確認起始位置
例如：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) 就功能而言，此函數與replace有些相似。但需要注意的一點是，translate是絕對匹配替換，這點與replace函數具有非常大區別。什么是絕對匹配替換呢？簡單的說，是將字符串c1中按一定的格式c2替換為c3。如果文字形容仍然無法理解，我們通過幾具實例來說明：
例如：
SELECT TRANSLATE('What is this','','-') FROM DUAL;
SELECT TRANSLATE('What is this','-','') FROM DUAL;
結果都是空。來試試這個：
SELECT TRANSLATE('What is this',' ',' ') FROM DUAL;
再來看這個：
SELECT TRANSLATE('What is this','ait','-*') FROM DUAL;
是否明白了點呢？Replace函數理解比較簡單，它是將字符串中指定字符替換成其它字符，它的字符必須是連續的。而translate中，則是指定字符串c1中出現的c2，將c2中各個字符替換成c3中位置順序與其相同的c3中的字符。明白了？Replace是替換，而translate則像是過濾
(三).字符型函數返回數字值(Character Functions Returning Number Values)
本類函數支持所有的數據類型
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;的返回值是什么
(四).日期函數(Datetime Functions)
本類函數中，除months_between返回數值外，其它都將返回日期。
1、ADD_MONTHS() 返回指定日期月份+n之后的值，n可以為任何整數。
例如：SELECT ADD_MONTHS(sysdate,12),ADD_MONTHS(sysdate,-12) FROM DUAL;
2、CURRENT_DATE 返回當前session所在時區的默認時間
例如：
SQL> alter session set nls_date_format = 'mm-dd-yyyy' ;
SQL> select current_date from dual;
3、SYSDATE 功能與上相同，返回當前session所在時區的默認時間。但是需要注意的一點是，如果同時使用sysdate與current_date獲得的時間不一定相同，某些情況下current_date會比sysdate快一秒。經過與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默認時區中的星期形式相同。
例如：三思用的中文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;
則會執行出錯，提供你說周中的日無效，就是這個原因了。
6、MONTHS_BETWEEN(d1,d2) 返回d1與d2間的月份差，視d1,d2的值大小，結果可正可負，當然也有可能為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]) 前面講數值型函數的時候介紹過ROUND，此處與上功能基本相似，不過此處操作的是日期。如果不指定fmt參數，則默認返回距離指定日期最近的日期。
例如：SELECT ROUND(SYSDATE,'HH24') FROM DUAL;
8、TRUNC(d[,fmt]) 與前面介紹的數值型TRUNC原理相同，不過此處也是操作的日期型。
例如：SELECT TRUNC(SYSDATE,'HH24') FROM DUAL;
(五).轉換函數(Conversion Functions)
轉換函數將指定字符從一種類型轉換為另一種，通常這類函數遵循如下慣例：函數名稱后面跟著待轉換類型以及輸出類型。
1、TO_CHAR() 本函數又可以分三小類，分別是
 轉換字符->字符TO_CHAR(c)：將nchar,nvarchar2,clob,nclob類型轉換為char類型；
例如：SELECT TO_CHAR('AABBCC') FROM DUAL;
 轉換時間->字符TO_CHAR(d[,fmt])：將指定的時間(data,timestamp,timestamp with time zone)按照指定格式轉換為varchar2類型；
例如：SELECT TO_CHAR(sysdate,'yyyy-mm-dd hh24:mi:ss') FROM DUAL;
 轉換數值->字符TO_CHAR(n[,fmt])：將指定數值n按照指定格式fmt轉換為varchar2類型并返回；
例如：SELECT TO_CHAR(-100, 'L99G999D99MI') FROM DUAL;
2、TO_DATE(c[,fmt[,nls]]) 將char,nchar,varchar2,nvarchar2轉換為日期類型，如果fmt參數不為空，則按照fmt中指定格式進行轉換。注意這里的fmt參數。如果ftm為'J'則表示按照公元制(Julian day)轉換，c則必須為大于0并小于5373484的正整數。
例如：
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中指定格式轉換為數值類型并返回。
例如：SELECT TO_NUMBER('-100.00', '9G999D99') FROM DUAL;
(六).其它輔助函數(Miscellaneous Single-Row Functions)
1、DECODE(exp,s1,r1,s2,r2..s,r[,def]) 可以把它理解成一個增強型的if else，只不過它并不通過多行語句，而是在一個函數內實現if else的功能。
exp做為初始參數。s做為對比值，相同則返回r，如果s有多個，則持續遍歷所有s，直到某個條件為真為止，否則返回默認值def(如果指定了的話)，如果沒有默認值，并且前面的對比也都沒有為真，則返回空。
毫無疑問，decode是個非常重要的函數，在實現行轉列等功能時都會用到，需要牢記和熟練使用。
例如：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也是個很有意思的函數。邏輯等價于：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轉換為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) 該函數只能應用于樹狀查詢。返回通過c1連接的從根到節點的路徑。該函數必須與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的指定參數c2的值按照指定長度n截取后返回。
Oracle9i提供內置了一個命名空間USERENV，描述了當前session的各項信息，其擁有下列參數：
 CURRENT_SCHEMA:當前模式名；
 CURRENT_USER:當前用戶；
 ip_ADDRESS:當前客戶端IP地址；
 OS_USER:當前客戶端操作系統用戶；
等等數十項，更詳細的參數列還請大家直接參考Oracle Online Documents
例如：SELECT SYS_CONTEXT('USERENV', 'SESSION_USER') FROM DUAL;
注：N表示數字型，C表示字符型，D表示日期型，[]表示內中參數可被忽略，fmt表示格式。
單值函數在查詢中返回單個值，可被應用到select，where子句，start with以及connect by 子句和having子句。
