Oracle 基本函數與Stored Procedures介紹
Oracle是以高級結構化查詢語言為基礎的大型關連性資料庫(RDBMS)，也是目前最被廣泛利用的Client/Server結構的資料庫系統，可以用來存放大量的資料；我們會在資料庫中建立各種不同的表格，定義出自己想要的結構，方便我們進行儲存與讀取。
然而，對於初學者來說，往往對於Oracle的函數使用不甚了解，寧可直接將資料全部取出以後，再用程式語言針對自己想要的資料進行過濾；或者是分批進入自己想要的表格中撈取資料，並且再用程式語言進行整合，如此完全浪費了Oracle所提供的諸多函數與關鍵字的功能，同時也加重了程式的負擔。
因此，善用函數與Stored Procedures，不僅可以加速我們程式中的效能，避免多次的資料庫搜尋，進而增進程式處理的速度。

Oracle基本函數
Oracle提供了許多好用的基本函數，大部分與一般程式語言大同小異，在此將針對比較常用的做個簡介。

1. Trim, RTrim, LTrim(原字串, 字串符)
Trim的使用效果與一般程式語言中的Trim()一樣，Trim用於消除兩邊空白，RTrim與LTrim可以用於消除其他字符串，若不指定要消除的字符串，則預設消除空白。
範例： Trim(‘ 112 ’) -.jpg'> 112 & RTrim(‘121’,’1’) -.jpg'> 12 & LTrim(‘121’,’1’) -.jpg'> 21

RPAD, LPAD(原字串, 長度, 字符) : RPAD與LPAD會補足字串符到指定長度。
範例： RPAD(‘112’,9,’*’) -.jpg'> 112******

Oracle的欄位Type有char與varchar的差別，char欄位若長度不足時，會補足空白到指定的長度，因此若兩個欄位有此差別，就會需要使用此函數，以避免擷取不到指定的資料。
範例： Select * From Table1 A Left Join Table2 B on Trim(A.Type) = Trim(B.Type)

2. SubStr(string, start, count)
此函數也如同字面意思一樣，用來從原字串指定起始位置後，取得指定長度字串。除了在擷取資料時可使用以外，同時也可以當作條件來使用；舉例來說，要利用身分證擷取出生地為台北的男性就可以使用。
範例： Where SubStr(table1.IDNumber, 1, 2) = ‘F1’

3. InStr(原字串, 欲搜索字串, 起始位置, 出現位置)
InStr主要是用來尋找指定字串在原字串中的位置，回傳值是數字，當中起始位置可以為-1代表從後面開始尋找，出現的位置可以指定他出現第幾次的位置。
此方法感覺起來沒有實質上的用途，但是可以與其他函數進行搭配而達成一些特殊的要求；例如，我們想要擷取一串數字到最後一個0，並且捨棄最後一個0後面的數字，那我們就可以使用此方法。
範例： Select SubStr('120405', 0, InStr('120405','0',-1)) from dual -.jpg'> 12040

我們藉由InStr去找到原字串最後一個0的位置，接著利用上面介紹過的SubStr去取得指定的長度串，進而達到需求，就不需利用程式語言再去進行額外的處理。
Length : 同InStr一樣回傳數字，但是會回傳字串的總長度。
範例： Length(‘112353’) -.jpg'> 6

4. 字串處理
i. Concat : 連接兩個字串符，也可以使用||。
範例： Select Concat(‘good to ’, ‘see ’) || ‘you’ from dual-.jpg'> good to see you

ii. InitCap : 將第一個字轉為大寫，可用於名字上。
範例： Select InitCap(‘ivan’) from dual -.jpg'> Ivan

iii. Lower & Upper : 將字串全部轉為大寫或小寫。
範例： Select Lower(‘UpPeR’) from dual -.jpg'> upper

iv. Replace : 將字串裡的字進行替換。
範例： Select Replace(‘He love dog’, ‘He’, ‘I’) from dual -.jpg'> I love dog

v. Greatest & Least : 回傳一組表達式中的最大值或是最小值，會比較字符編碼的大小。
範例： Select Greatest(‘BA’, ‘BC’, ‘AB’) from dual -.jpg'> BC

5. 轉換處理
vi. To_Date : 將其他資料格式轉為日期格式，須注意格式是否符合。
範例： Select To_Date(‘20130401’,’yyyyMMdd’) from dual

vii. To_Char : 將其他資料格式轉換為字串格式。
範例： Select To_Char(sysdate, ‘yyyyMMdd’) from dual -.jpg'> 20130401

viii. To_Number : 將其他資料格式轉為數字格式，須注意格式是否符合。
範例： Select To_Number(‘20156’) from dual -.jpg'> 20156

6. 數值處理
數值處理上，與一般程式語言所提供的算術運算資料庫一樣，Oracle也提供了許多運算方式，除了基本的加減乘除以外也提供了Sum、Max、Min、Abs、Avg、Ceil、Floor等，這些數值的處理讓我們可以做完運算之後接續進行過濾的動作，而不需再利用程式語言進行額外的動作。

7. 日期處理
ix. Add_Months : 增加或減去月份，給予負值則為減去月份。
範例： Select To_Char (
Add_Months ( To_Date('20121228','yyyyMMdd') , 2 ), 'yyyyMMdd')
from dual -.jpg'> 20130228

x. Last_Day : 返回日期的最後一天。
範例： Select To_Char ( Last_Day(sysdate) , ‘yyyyMMdd’) from dual -.jpg'> 20130430

xi. Months_Between : 顯示出兩個日期所差異的月份，若前者日期較新，則為正值；反之則為負值。
範例： Select Months_Between( date’2013-03-31’, date’2013-02-28’) from dual -.jpg'> 1

xii. Next_Day : 回傳當前日期的下一個指定日期，不包含當天。
範例： Select To_Char(Next_Day ( date ‘2013-04-01’ , ‘星期一’ ) , ’yyyyMMdd’) from dual -.jpg'> 20130408

xiii. Sysdate : 取得當前系統日期，因為是隨著日期變動，所以可以很方便的拿來當做判斷式，在Oracle中也是很常用的一個功能。

8. 其餘重要且讓使用者操作更方便的函數
xiv. Group By : 將一些欄位做為條件，並針對想要的數值進行統計。
範例： Select Location, Gender, Sum(Salary) from CompanyEmployee
Group By Location, Gender
以上的SQL式子即抓出，公司中各個地點的男性與女性分別的薪資總合。

xv. Order By : 針對指定的欄位進行排序。
範例： Select Account, ApplyDate, Price, Amount from tableA
Order By Account, ApplyDate
以上的SQL式子會依照Account做為排序，Account遭遇相同時，再依照ApplyDate進行排序。

xvi. Having : Having子句會在結果集找出來以後才去搜尋，因此要搭配where子句先做第一步的篩選，減少了資料量，再使用Having子句負擔才會比較輕。
Having子句的優點就在於結果集出來以後才去篩選，where則是撈取資料時就進行篩選，兩者的時機是不同的，如果不使用Having就必須使用子查詢的方式。
範例： Select table_name, count(*)
from all_index
Group By table_name
Having count(*) = 2
這樣的一個Having子句，會在計算完各table的index數量以後，將數量為2的table_name顯示出來。
我們若直接使用where count(*) = 2 則會產生錯誤。

Oracle當然不只提供了上述的函數，在此僅列出常用的部分，這些函數有些都可以同時運用，以達成使用者各種不同的要求，因此熟稔各個函數的使用，將有助於完成各項需求。

Stored Procedures
雖然Oracle已經提供了許多的函數，但是仍有許多的功能是達不到的。舉例來說，程式語言中有迴圈的功能，但是在單一SQL式子中卻沒有此功能；又或者，如果是一個大型的資料庫，我們不可能將所有表格間都做出相關性的功能，同時，如果要將所有表格都建立相關性，並且想要使用Join的方式進行串接資料，也容易使得單一SQL式子過於複雜，並且難以維護。如以上所述，單一SQL式子沒辦法明確的達成我們需求的時候，我們可以使用Stored Procedures來達成。

在Stored Procedures中，可以使用的功能，簡單舉例如下：

1. IF
在IF的語法中，同樣可以用多層的IF ELSIF ELSE語法，但是要注意的是，一個If都要搭配一個End If代表終結。另外在單一SQL式子中，想要使用If的功能也可以利用Case When或Decode的方式去達成，缺點是無法使用自定義的變數去當作條件。
範例： if (Var1 = Var2) then
　　if not (Var3 = Var4) then
　 xxxxxxxxx
　 else
　 xxxxxxxxx
　　end if;
end if;

2. FOR loop
在Stored Procedure中，我們可以使用迴圈來達成某些功能，這是在單一SQL語法中所達不到的，會有這樣的需求在於，在Stored Procedure中，我們可能會有Cursor預先撈出一些資料，接著要再對這些資料集做額外操作時，就可以使用迴圈的方式進行，用法跟一般程式語言類似，其標準使用格式如下：
For loop_counter In [REVERSE] lowest_number … highest_number
Loop
{statement}
End Loop;

範例：


3.Cursor
Cursor是內建的一種Pointer，可用來擷取整個資料的集合，並讓使用者可以針對各種資料進行存取。如同之前所述，我們有時並不想使用過多的Join去將所有的表格串起，過多的Join不只是徒增複雜度，同時也會讓效能降低；或者是我們要針對不同條件去選取資料並進行操作，在這些情況下，我們都可以使用Cursor預先撈出一個資料的集合，並針對這個集合做額外的操作。預設的Cursor數量為50個，但是我們可以在起始參數裡面進行調整 (”OPEN_CURSOR”)
範例：


4. dbms_output.put_line
這個部分與Stored Procedures無關，而是提供了一種Stored Procedures的除錯方式，如果只安裝了Oracle一套軟體，無其他輔助工具，則Stored Procedure的除錯會是個很大的困擾。要去除錯Stored Procedures除了利用分段檢視以外，另一個很好用的方式就是在Stored Procedures中安插數個dbms_output.put_line，我們就可以利用DBMS輸出去找出錯誤的段落，針對那個段落進行修改，將可減少很多時間。
範例：


5. 其他功能
i. 可以在一個Stored Procedure中去呼叫其他的Stored Procedure或是Function，這樣增加了許多的彈性，因為把大量的功能集合在單一的Stored Procedure中會增加閱讀的困難性，並且很難去除錯。
範例：


ii. 可以在Stored Procedures中段就執行Insert, Update, Delete等操作，直接去更動DB，並使用其中的值。
範例：


總合來說，Stored Procedures可以使用各式的函式，又提供了許多更貼近程式語言的功能，所以比起只使用單一SQL會更容易達成各項需求，雖然一開始要上手是有些微門檻存在，但是常使用後就會發現其便利性，並且能體會其強大的能力。

總結
Oracle是最被廣泛使用的資料庫系統，但是在不熟Oracle函數的情況下，將降低使用Oracle的效率；在我們熟知函數與Stored Procedures的使用之後，我們可以直接在撈取資料時取得我們想要的資料，而不需撈取大量的資料以後再進行一次額外的篩選。

函數的使用上，針對不同的需求有時需要互相的配合，並且要注意到欄位的型態，轉換上則要多注意格式的正確性，扣除本文所介紹的部分，還有許多其他的函數，例如: Join, Case When等，使用Join需小心是否會造成虛增，使用Case When時則要注意對應關係，其餘的部分就只能在使用時多加注意。

Stored Procedures相當於一個儲存在資料庫中的子程式，同時可供外部程式使用，如此一來，將可減少了其他外部程式需要多次進入資料庫進行存取的動作，同時Stored Procedure寫入資料庫時需先進行編譯的動作，因此在同樣的語法之下，其效能上會比起一般的Query還要快速，而且不同的外部程式也可以重複使用此Stored Procedure，對於程式上有較佳的維護性。但是，Stored Procedure畢竟不是真正的高階程式語言，因此提供的方法上以及上手程度都會比較差，需要多下一番功夫。所以，如果能真正熟稔Stored Procedure的使用，對於Oracle的認識度也將有大幅度的提升。

作者簡介：
康惟翔，現任職於凌群電腦金融產品研發處，主要專長為Windows Form程式開發、Oracle資料庫語言撰寫與管理、Windows Server管理與問題處理、Team Foundation Server、版本控制等，並參與越南券商相關專案開發。