循序漸進學Oracle之函數（重點）

單行函數—字元函數

雖然各個資料庫都是支援SQL語句的，但是每一個資料庫也有每一個資料庫自己所支援的操作函數，這些就是單行函數，而如果要想進行資料庫開發的話，除了要會使用SQL之外，就是要多學習函數。

單行函數主要分為以下五類：字元函數、數字函數、日期函數、轉換函式、通用函數；

1-字元函數：

字元函數的功能主要是進行字串資料的操作，下面給出幾個字元函數：

* UPPER(字串|列):將輸入的字串變為大寫返回;

* LOWER(字串|列)：將輸入的字串變為小寫返回；

* INITCAP(字串|列)：開頭首字母大寫;

* LENGTH(字串|列)：求出字串的長度；

* REPLACE(字串|列)：進行替換；

*SUBSTR(字串|列,開始點[結束點])：字串截取：

Oracle之中有一點比較麻煩，即使要驗證字串，也必須編寫完整的SQL語句，所以在Oracle資料庫之中為了使用者的查詢方便，故專門提供了一個“dual”的虛擬表。

範例1：觀察轉大寫的函數

SELECT UPPER('hello') FROM dual ;

650) this.width=650;" style="border-bottom:0px;border-left:0px;border-top:0px;border-right:0px" title="clip_image002" border="0" alt="clip_image002" src="http://www.bkjia.com/uploads/allimg/131229/125604G30-0.jpg" height="137" />

大寫轉換的用處： 在一般的使用之中，使用者輸入資料的時候去關心資料本身存放的是大寫還是小寫呢？

SELECT * FROM emp WHRER ename='&str';

此時如果輸入的是小寫，則肯定無法查詢出資料，所以這個時候不能要求使用者這麼,故這個時候只能由程式自己去適應，加入一個函數：

SELECT * FROM emp WHERE ename=UPPER('&str');

當然以上的“&”與）操作屬於替代變數的內容，此部分內容不做重點。

650) this.width=650;" style="border-bottom:0px;border-left:0px;border-top:0px;border-right:0px" title="clip_image004" border="0" alt="clip_image004" src="http://www.bkjia.com/uploads/allimg/131229/1256042937-1.jpg" height="256" />

範例2：觀察轉小寫操作，將所有的僱員姓名按照小寫字母返回；

SELECT LOWER('ename') FROM emp ;

650) this.width=650;" style="border-bottom:0px;border-left:0px;border-top:0px;border-right:0px" title="clip_image006" border="0" alt="clip_image006" src="http://www.bkjia.com/uploads/allimg/131229/1256042Y4-2.jpg" height="362" />

範例3：將每一個僱員姓名的開頭首字母大寫  

SELECT INITCAP(ename) FROM emp ;

650) this.width=650;" style="border-bottom:0px;border-left:0px;border-top:0px;border-right:0px" title="clip_image008" border="0" alt="clip_image008" src="http://www.bkjia.com/uploads/allimg/131229/1256046248-3.jpg" height="355" />

範例4：查詢每個僱員姓名的長度

SELECT ename,LENGTH(ename) FROM emp ;

650) this.width=650;" style="border-bottom:0px;border-left:0px;border-top:0px;border-right:0px" title="clip_image010" border="0" alt="clip_image010" src="http://www.bkjia.com/uploads/allimg/131229/125604D55-4.jpg" height="357" />

範例5：要求查詢出僱員姓名長度正好是5的資訊；

SELECT ename,LENGTH(ename) FROM emp WHERE LENGTH(ename)=5 ;

650) this.width=650;" style="border-bottom:0px;border-left:0px;border-top:0px;border-right:0px" title="clip_image012" border="0" alt="clip_image012" src="http://www.bkjia.com/uploads/allimg/131229/1256046131-5.jpg" height="264" />

範例6：使用字母“_”替換姓名中所有字母“A”的資訊；

col REPLACE(ename,'A','_') FORMAT A50 ;SELECT REPLACE(ename,'A','_') FROM emp ;

650) this.width=650;" style="border-bottom:0px;border-left:0px;border-top:0px;border-right:0px" title="clip_image014" border="0" alt="clip_image014" src="http://www.bkjia.com/uploads/allimg/131229/1256042608-6.jpg" height="374" />

字串截取操作有兩種文法：

|-文法一：SUBSTPR字串|列，開始點），表示從開始點一直截以到結尾；

|-文法二：SUBSTPR字串|列，開始點，結束點），表示從開始點一直截以到結束點，截取部分內容；

範例7: 文法一：SUBSTPR字串|列，開始點），表示從開始點一直截以到結尾；

SELECT ename,SUBSTR(ename,3) FROM emp ; //從第3個字元開始一直到結尾！

650) this.width=650;" style="border-bottom:0px;border-left:0px;border-top:0px;border-right:0px" title="clip_image016" border="0" alt="clip_image016" src="http://www.bkjia.com/uploads/allimg/131229/1256043261-7.jpg" height="358" />

範例8：文法二：SUBSTPR字串|列，開始點，結束點），表示從開始點一直截以到結束點，截取部分內容；

SELECT ename,SUBSTR(ename,0,3) FROM emp ; //截取前3個字元！SELECT ename,SUBSTR(ename,1,3) FROM emp ; //截取前3個字元！

650) this.width=650;" style="border-bottom:0px;border-left:0px;border-top:0px;border-right:0px" title="clip_image018" border="0" alt="clip_image018" src="http://www.bkjia.com/uploads/allimg/131229/125604C12-8.jpg" height="355" />

650) this.width=650;" style="border-bottom:0px;border-left:0px;border-top:0px;border-right:0px" title="clip_image020" border="0" alt="clip_image020" src="http://www.bkjia.com/uploads/allimg/131229/125604MC-9.jpg" height="357" />

範例9：要求截取每個僱員姓名的後三個字母；

* 正常思路：通過長度-2確定開始點

SELECT ename,SUBSTR(ename,LENGTH(ename)-2) FROM emp ;

* 新思路：設定負數，表示從後指定截取位置

SELECT ename,SUBSTR(ename,-3) FROM emp ;

650) this.width=650;" style="border-bottom:0px;border-left:0px;border-top:0px;border-right:0px" title="clip_image022" border="0" alt="clip_image022" src="http://www.bkjia.com/uploads/allimg/131229/12560440U-10.jpg" height="353" />

650) this.width=650;" style="border-bottom:0px;border-left:0px;border-top:0px;border-right:0px" title="clip_image024" border="0" alt="clip_image024" src="http://www.bkjia.com/uploads/allimg/131229/1256041561-11.jpg" height="359" />

面試題：

1、請問SUBSTR）函數截取的時候下標是從0,還是從1開始？

* 在Oracle資料庫之中，SUBSTR）函數從0或1開始都是一樣的；見範例8）

* SUBSTR）函數也可以設定為負數，表示由後指定截取開始點；見範例9）

2-數字函數
數字函數一共有3個：

* ROUND(數字|列[,保留小數的位元])：四捨五入的操作；

* TRUNC(數字|列[,保留小數的位元])：捨棄指定位置的內容；

* MOD(數字1，數字2)：模數，取餘數；

範例10：驗證ROUND()函數  

SELECT ROUND(903.5) FROM dual ;

650) this.width=650;" style="border-bottom:0px;border-left:0px;border-top:0px;border-right:0px" title="clip_image026" border="0" alt="clip_image026" src="http://www.bkjia.com/uploads/allimg/131229/1256043536-12.jpg" height="119" />

SELECT ROUND(903.5),ROUND(-903.53567) FROM dual ;SELECT ROUND(903.5),ROUND(-903.53567),ROUND(903.53567,-1) FROM dual ;SELECT ROUND(903.5),ROUND(-903.53567),ROUND(903.53567,-1),ROUND(903.53567,2) FROM dual ;//保留2位

650) this.width=650;" style="border-bottom:0px;border-left:0px;border-top:0px;border-right:0px" title="clip_image028" border="0" alt="clip_image028" src="http://www.bkjia.com/uploads/allimg/131229/1256041919-13.jpg" height="316" />

範例11：驗證TRUNC()函數

SELECT TRUNC(903.5),TRUNC(-903.53567),TRUNC(903.53567,-1),TRUNC(903.53567,2) FROM dual ;

650) this.width=650;" style="border-bottom:0px;border-left:0px;border-top:0px;border-right:0px" title="clip_image030" border="0" alt="clip_image030" src="http://www.bkjia.com/uploads/allimg/131229/1256042R3-14.jpg" height="110" />

範例12：驗證MOD()函數    

SELECT MOD(10,3) FROM dual ;

650) this.width=650;" style="border-bottom:0px;border-left:0px;border-top:0px;border-right:0px" title="clip_image032" border="0" alt="clip_image032" src="http://www.bkjia.com/uploads/allimg/131229/1256042357-15.jpg" height="119" />

以上的三個主要的數學函數，在學習Java中也會有相匹的內容！

2-日期函數

如果現在要想進行日期的操作，則首先有一個必須要解決的問題，就是如何取得當前的日期, 這個當前日期可使用“SYSDATE”取得，代碼如下：  

SELECT SYSDATE FROM dual ;

650) this.width=650;" style="border-bottom:0px;border-left:0px;border-top:0px;border-right:0px" title="clip_image034" border="0" alt="clip_image034" src="http://www.bkjia.com/uploads/allimg/131229/1256044359-16.jpg" height="118" />

範例13：除了以上的當前日期之外，在日期中也可以進行若干計算：

* 日期 + 數字 = 日期，表示若干天之後的日期；  

SELECT SYSDATE+3,SYSDATE+300 FROM dual ;

* 日期 - 數字 = 日期，表示若干天前的日期；    

SELECT SYSDATE-3,SYSDATE-300 FROM dual ;

* 日期 – 日期 = 數字，表示的是兩個日期的天數，但是肯定是大日期 – 小日期；

範例14：求出每個僱員到今天為止的僱傭天數；    

SELECT ename,hiredate,SYSDATE-hiredate FROM emp ;

650) this.width=650;" style="border-bottom:0px;border-left:0px;border-top:0px;border-right:0px" title="clip_image036" border="0" alt="clip_image036" src="http://www.bkjia.com/uploads/allimg/131229/1256042J0-17.jpg" height="211" />

650) this.width=650;" style="border-bottom:0px;border-left:0px;border-top:0px;border-right:0px" title="clip_image038" border="0" alt="clip_image038" src="http://www.bkjia.com/uploads/allimg/131229/1256044132-18.jpg" height="354" />

註：而且很多程式設計語言之中，也都會提出一種概念，日期可以通過數字表示出來！

除了以上三個公式之外，也提供了以下四個操作函數：

* LAST_DAY(日期)：求出指定日期的最後一天；

範例15：求出本月的最後一天日期    

SELECT LAST_DAY(SYSDATE) FROM dual ;

* NEXT_DAY(日期,星期數)：求出下一個指定星期X的日期；

範例16：求出下一個周一    

SELECT NEXT_DAY(SYSDATE,'星期一') FROM dual ;

* ADD_MONTHS(日期,數字)：求出若干月之後的日期；

範例17：求出4個月後是何時    

SELECT ADD_MONTHS(SYSDATE,4) FROM dual ;

* MONTHS_BETWEEN(日期1,日期2)：求出兩個日期之間所經曆的月份；

範例18：求出每個僱員到今天為止的僱傭月份；    

SELECT ename,hiredate,MONTHS_BETWEEN(SYSDATE,hiredate) FROM emp ;SELECT ename,hiredate,TRUNC(MONTHS_BETWEEN(SYSDATE,hiredate)) FROM emp ;

650) this.width=650;" style="border-bottom:0px;border-left:0px;border-top:0px;border-right:0px" title="clip_image040" border="0" alt="clip_image040" src="http://www.bkjia.com/uploads/allimg/131229/1256041B0-19.jpg" height="313" />

650) this.width=650;" style="border-bottom:0px;border-left:0px;border-top:0px;border-right:0px" title="clip_image042" border="0" alt="clip_image042" src="http://www.bkjia.com/uploads/allimg/131229/12560430S-20.jpg" height="693" />

在所有的開發之中，如果是日期的操作，建議使用以上函數，因為這些函數可以避免閏年的問題。

4-轉換函式(核心)

現在已經接觸到了Oracle資料庫之中的三種資料:數字(NUMBER)、字串(VACHAR2)、日期(DATE)，轉換函式的主要功能是完成這幾種資料間的相互轉換的操作，一共有三種轉換函式：

* TO_CHAR(字串|列，格式字串)：將日期或者是數字變為字串顯示；

* TO_DATE(字串，格式字串)：將字串變為DATE資料顯示；

* TO_NUMBER(字串)：將字串變為數字顯示；

1、TO_CHAR()函數

在之前查詢過當前的系統日期時間：  

SELECT SYSDATE FROM dual ;

這個時候是按照“日-月-年”的格式顯示，很明顯此格式不符合於正常的思路，正常是“年-月-日”，

範例19：TO_CHAR()函數，但是使用此函數的話需要一些格式字串：年(yyyy),月(mm),日(dd)

SELECT TO_CHAR(SYSDATE,'yyyy-mm-dd'),TO_CHAR(SYSDATE,'yyyy') year,TO_CHAR(SYSDATE,'mm') month,TO_CHAR(SYSDATE,'dd') day FROM dual ;

650) this.width=650;" style="border-bottom:0px;border-left:0px;border-top:0px;border-right:0px" title="clip_image044" border="0" alt="clip_image044" src="http://www.bkjia.com/uploads/allimg/131229/1256044425-21.jpg" height="103" />

範例20：但是這個時候的顯示資料之中可以發現會存在前置0，如果要想消除這個0的話，可以加入一個“fm”。    

SELECT TO_CHAR(SYSDATE,'fmyyyy-mm-dd') FROM dual ;

650) this.width=650;" style="border-bottom:0px;border-left:0px;border-top:0px;border-right:0px" title="clip_image046" border="0" alt="clip_image046" src="http://www.bkjia.com/uploads/allimg/131229/1256044560-22.jpg" height="117" />

範例21：正常人都加0,故這個標記知道就行了，可是在Oracle之中，DATE裡面是包含了時間的，但是之前的代碼沒有顯示出時間，要想顯示時間，則需要增加標記：    

SELECT TO_CHAR(SYSDATE,'fmyyyy-mm-dd hh:mi:ss')day FROM dual ; //12時制SELECT TO_CHAR(SYSDATE,'fmyyyy-mm-dd hh24:mi:ss') day FROM dual ;

650) this.width=650;" style="border-bottom:0px;border-left:0px;border-top:0px;border-right:0px" title="clip_image048" border="0" alt="clip_image048" src="http://www.bkjia.com/uploads/allimg/131229/1256043215-23.jpg" height="118" />

範例22：一定要注意：使用TO_CHAR()函數之後，所有的內容都是字串，不再是之前的DATE型資料，TO_CHAR()函數也可以用數位格式化上，此時每一個“9”表示一位元字的概念，而不是數字9概念。    

SELECT TO_CHAR(11157191115719,'999,999,999,999,999') FROM dual ;SELECT TO_CHAR(11157191115719,'L999,999,999,999,999') FROM dual ;

其中的字母“L”，表示的是“Locale”的含義，即：當前的所在語言環境下的貨幣符號。

650) this.width=650;" style="border-bottom:0px;border-left:0px;border-top:0px;border-right:0px" title="clip_image050" border="0" alt="clip_image050" src="http://www.bkjia.com/uploads/allimg/131229/1256041613-24.jpg" height="217" />

2、TO_DATE()函數

範例23：此函數的主要功能是將一個字串變為DATE型資料。    

col TO_DATE('1988-8-8','yyyy-mm-dd') format a50 ;SELECT TO_DATE('1988-8-8','yyyy-mm-dd') FROM dual ;

650) this.width=650;" style="border-bottom:0px;border-left:0px;border-top:0px;border-right:0px" title="clip_image052" border="0" alt="clip_image052" src="http://www.bkjia.com/uploads/allimg/131229/125604AQ-25.jpg" height="131" />

一般此函數在更新資料時，使用比較多！

3、TO_NUMBER()函數：基本不用！

範例24：TO_NUMBER()函數一看就知道是將字串變為數位：    

SELECT TO_NUMBER('1') + TO_NUMBER('2') + TO_NUMBER('3') FROM dual ;

650) this.width=650;" style="border-bottom:0px;border-left:0px;border-top:0px;border-right:0px" title="clip_image054" border="0" alt="clip_image054" src="http://www.bkjia.com/uploads/allimg/131229/12560464b-26.jpg" height="114" />

範例25：但是在Oracle之中是很智能的，故以上功能不使用TO_NUMBER()函數也可以完成。    

SELECT '1' + '2' + '3' FROM dual ;

650) this.width=650;" style="border-bottom:0px;border-left:0px;border-top:0px;border-right:0px" title="clip_image056" border="0" alt="clip_image056" src="http://www.bkjia.com/uploads/allimg/131229/1256045113-27.jpg" height="115" />

重點：

* TO_NUMBER()函數，基本已經不考慮了；

* TO_CHAR()函數，是重點；

* TO_DATE()函數，是次重點。

5-通用函數(核心)

通用函數主要有兩個：NVL）、DECODE()，這兩個函數算是 Oracle自己的特色函數了；

1、NVL()函數，處理null

範例26：：要求查詢出每個僱員的全部年薪    

SQL> SELECT ename,sal,comm,(sal+comm)*12 年薪 FROM emp ;

650) this.width=650;" style="border-bottom:0px;border-left:0px;border-top:0px;border-right:0px" title="clip_image058" border="0" alt="clip_image058" src="http://www.bkjia.com/uploads/allimg/131229/125604A07-28.jpg" height="360" />

範例27：由上可知，有僱員的年薪變為null了，而造成這種問題的關鍵是在於comm欄位上為null，那麼要想解決這個問題，就必須做一種處理：將null變為0，而這個就是NVL()函數作用。    

SELECT ename,sal,comm,(sal+comm)*12,NVL(comm,0) FROM emp ;

650) this.width=650;" style="border-bottom:0px;border-left:0px;border-top:0px;border-right:0px" title="clip_image060" border="0" alt="clip_image060" src="http://www.bkjia.com/uploads/allimg/131229/1256045025-29.jpg" height="378" />

範例28：看來，年薪還有為0的，下面再來看看！    

SQL> SELECT ename,sal,comm,(sal+NVL(comm,0))*12 ?êD? FROM emp ;

650) this.width=650;" style="border-bottom:0px;border-left:0px;border-top:0px;border-right:0px" title="clip_image062" border="0" alt="clip_image062" src="http://www.bkjia.com/uploads/allimg/131229/1256045963-30.jpg" height="357" />

2、DECODE()函數：多數值判斷

DECODE()函數非常類似於程式中的if…else語句，唯一不同的是DECODE()函數判斷的是數值，而不是邏輯條件了。

例如：要求顯示全部僱員的職位，但是這些職位要求替換成中文顯示：

* CLERK：辦事員；

* SALESMAN：銷售；

* MANAGER：經理；

* ANALYST：分析員；

* PRESIDENT：總裁；

這種判斷肯定是逐行判斷，故此時必須採用DECODE()函數，而此函數文法如下：  

DECODE(數值|列，判斷1，顯示值1，判斷2，顯示值2，判斷3，顯示值3，...)

範例29：實現顯示的操作功能    

SELECT empno,ename,job,DECODE(job,'CLERK','辦事員','SALESMAN','銷售人員','MANAGER','經理','ANALYST','分析員','PRESIDENT','總裁') 職務 FROM emp ;

650) this.width=650;" style="border-bottom:0px;border-left:0px;border-top:0px;border-right:0px" title="clip_image064" border="0" alt="clip_image064" src="http://www.bkjia.com/uploads/allimg/131229/1256045244-31.jpg" height="344" />

DECODE()函數是整個Oracle之中最具有特點的函數，必須掌握！！！

總結篇：

1、SQL語句的基本格式，此處給出的只是最基本的文法：SELECT、FROM、WHERE、ORDER BY的關係；

2、記下SCOTT使用者中的所有的資料表的資訊，包括列的名稱，作用及類型；

3、Oracle中的幾個單行函數，一定要記下來，所有函數都要求使用。

本文出自 “beyondhedefang” 部落格，請務必保留此出處http://beyondhdf.blog.51cto.com/229452/1284698