第188期 / June 5, 2013

研發新視界

分享到臉書!分享到維特!分享到噗浪!分享到Google+!分享到微博!轉寄友人友善列印

Oracle 基本函數與Stored Procedures介紹

作者/康惟翔

[發表日期:2013/6/5]

前言

Oracle是以高級結構化查詢語言為基礎的大型關連性資料庫(RDBMS),也是目前最被廣泛利用的Client/Server結構的資料庫系統,可以用來存放大量的資料;我們會在資料庫中建立各種不同的表格,定義出自己想要的結構,方便我們進行儲存與讀取

然而,對於初學者來說,往往對於Oracle的函數使用不甚了解,寧可直接將資料全部取出以後,再用程式語言針對自己想要的資料進行過濾;或者是分批進入自己想要的表格中撈取資料,並且再用程式語言進行整合,如此完全浪費了Oracle所提供的諸多函數與關鍵字的功能,同時也加重了程式的負擔。

因此,善用函數與Stored Procedures,不僅可以加速我們程式中的效能,避免多次的資料庫搜尋,進而增進程式處理的速度。

Oracle基本函數

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

一、Trim, RTrim, LTrim(原字串, 字串符)

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

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

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


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

    Ex. Where SubStr(table1.IDNumber, 1, 2) = ‘F1’


三、InStr(原字串, 欲搜索字串, 起始位置, 出現位置)

    InStr主要是用來尋找指定字串在原字串中的位置,回傳值是數字,當中起始位置可以為-1代表從後面開始尋找,出現的位置可以指定他出現第幾次的位置。

    此方法感覺起來沒有實質上的用途,但是可以與其他函數進行搭配而達成一些特殊的要求;例如,我們想要擷取一串數字到最後一個0,並且捨棄最後一個0後面的數字,那我們就可以使用此方法。

    Ex. Select SubStr('120405', 0, InStr('120405','0',-1)) from dual-->12040

    我們藉由InStr去找到原字串最後一個0的位置,接著利用上面介紹過的SubStr去取得指定的長度串,進而達到需求,就不需利用程式語言再去進行額外的處理。

    Length : 同InStr一樣回傳數字,但是會回傳字串的總長度。
    Ex. Length(‘112353’)-->6

四、字串處理

    1.Concat : 連接兩個字串符,也可以使用||。
    Ex. Select Concat(‘good to ’, ‘see ’) || ‘you’ from dual--> good to see you

    2.InitCap : 將第一個字轉為大寫,可用於名字上。
    Ex. Select InitCap(‘ivan’) from dual--> Ivan

    3.Lower & Upper : 將字串全部轉為大寫或小寫。
    Ex. Select Lower(‘UpPeR’) from dual--> upper

    4.Replace : 將字串裡的字進行替換。
    Ex. Select Replace(‘He love dog’, ‘He’, ‘I’) from dual-->I love dog

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

五、轉換處理

    1.To_Date : 將其他資料格式轉為日期格式,須注意格式是否符合。
    Ex. Select To_Date(‘20130401’,’yyyyMMdd’) from dual

    2.To_Char : 將其他資料格式轉換為字串格式。
    Ex. Select To_Char(sysdate, ‘yyyyMMdd’) from dual --> 20130401

    3.To_Number : 將其他資料格式轉為數字格式,須注意格式是否符合。
    Ex. Select To_Number(‘20156’) from dual --> 20156

六、數值處理

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

七、日期處理

    1.Add_Months : 增加或減去月份,給予負值則為減去月份。
    Ex. Select To_Char (
    Add_Months ( To_Date('20121228','yyyyMMdd') , 2 ), 'yyyyMMdd')
    from dual -->20130228

    2.Last_Day : 返回日期的最後一天。
    Ex. Select To_Char ( Last_Day(sysdate) , ‘yyyyMMdd’) from dual--> 20130430

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

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

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

八、其餘重要且讓使用者操作更方便的函數

    1.Group By : 將一些欄位做為條件,並針對想要的數值進行統計。
    Ex. Select Location, Gender, Sum(Salary) from CompanyEmployee
    Group By Location, Gender
    以上的SQL式子即抓出,公司中各個地點的男性與女性分別的薪資總合。

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

    3.Having : Having子句會在結果集找出來以後才去搜尋,因此要搭配where子句先做第一步的篩選,減少了資料量,再使用Having子句負擔才會比較輕。

    Having子句的優點就在於結果集出來以後才去篩選,where則是撈取資料時就進行篩選,兩者的時機是不同的,如果不使用Having就必須使用子查詢的方式。

    Ex. 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中,可以使用的功能,簡單舉例如下:

一、IF

    在IF的語法中,同樣可以用多層的IF ELSIF ELSE語法,但是要注意的是,一個If都要搭配一個End If代表終結。另外在單一SQL式子中,想要使用If的功能也可以利用Case When或Decode的方式去達成,缺點是無法使用自定義的變數去當作條件。

    Ex. if (Var1 = Var2) then
    if not (Var3 = Var4) then
    xxxxxxxxx
    else
    xxxxxxxxx
    end if;
    end if;

二、FOR loop

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


三、Cursor

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

    Ex.


四、dbms_output.put_line

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

    Ex.


五、其他功能

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

    Ex.


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

    Ex.


總合來說,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的認識度也將有大幅度的提升。