Oracle基本使用與介紹

作者/康惟翔

前言 對於金融行業來說，數據的保留與處理皆十分的重要，因為一筆數據代表的不僅僅是一筆的資料，而是一份與錢有關的交易，所以處理上都需格外的小心，並確保資料的正確性，因為僅僅是一個欄位的數字發生了錯誤，所造成的傷害是需要花費大量精神與金錢進行修補。 另外在金融的體系下，並不是每一位投資人每天都會進行交易，但是其原有的庫存與金額卻仍要確實的保留，因此資料的維護也是十分重要，同時也需注意空間的議題，要有夠大的空間來儲存所有的舊資料與每日新增的上萬筆資料。 由以上可以知道，我們需要一個資料庫的軟體，來去儲存這些交易的資料，同時，這個資料庫軟體需要有夠大的資料儲存空間、一定的效能，並且要有足夠的安全性及良好的穩定性，同時資料也不允許隨意的遺失。 在挑選上，我們使用了Oracle做為使用的資料庫。Oracle 為目前世界上屬一屬二的軟體公司，同時為第一套使用關聯式資料庫管理系統（RDBMS）的軟體公司。自1980年代中期開始，即可在各個作業系統中見到Oracle。 Oracle具備了良好的平台獨立性，同時擁有開放式系統的精神，允許DBA可更改設定的資源，同時是唯一為了網絡計算而設計的資料庫，在關聯式資料庫的各式產品中，Oracle擁有著領先的技術，因此可以保持著領先的地位，這也是Oracle在市場中可以持續佔有主要地位的原因，而事實上，Oracle也已經成為世界上最大的RDBMS供應商，同時也是世界上最主要的資料處理軟體的供應商。 為何選擇Oracle 資料庫的軟體有許多，如：Access、MySQL、MSSQL、Oracle等。在眾多的資料庫軟體中，為什麼Oracle會比較適用於金融產業！將在下面針對Oracle做簡單的介紹。 金融行業其實算是一個資料量十分龐大的系統，每日可能都會有數以萬筆新的下單資料進來，加上一大堆的舊資料，我們所需要的資料庫系統必須具備的第一個能力就是龐大的儲存空間，如果一個資料庫的空間連儲存數以十萬計的投資人及其相關買賣資訊都做不到，則完全沒有辦法接受客戶去執行下單的動作。而Oracle提供很大的儲存空間，同時優化了資料的操作算法所以擁有較高的性能，適用於處理較多資料的問題，這同時對於我們需要針對資料進行計算時，可以得到很大的利處。 金融的系統，不可能只有一個使用者進行操作，依照規模，會有數以千百計的營業員進行下單的動作，因此會同時進行操作資料庫的動作，所以使用一個允許多用戶一起操作的資料庫是很重要的，而Oracle自第七版開始，引入了共享SQL和多線程服務器體系結構。此一方式減少了Oracle的資源佔用，並增強了Oracle的能力，讓其可以在低檔的機器平台上使用較少資源即可支持更多的用戶，在高檔的平台上則可以支持上千個用戶使用。 金融行業的資料是屬於個人隱私，需要被保密。同時，針對各個不同用戶，應提供各式的權限，如管理者與一般使用者應該要有所區別，而Oracle提供了角色分工的安全保密管理，在資料庫的管理功能上、完整性、一致性等各方面都有較好的處理，同時也獲得最高級別的ISO標準認證，提供一定程度的安全性保護。 在我們開發的過程中，常常需要取得資料庫中的資訊，而Oracle提供了與高階程式語言的接口，能在C#等主語言中嵌入SQL語句來對資料庫中的資料進行操作，讓資料庫的使用更為方便。 Oracle支持大量多媒體的資料型態，如：圖形、聲音等。此點在提供客戶照相功能時，可以幫助我們可以直接將影像的圖片進行轉變後存入DB。 在金融的系統上，每一筆資料都是很重要的，因為代表的是一筆筆的交易，不僅僅是一份記錄，每份交易關係到銀行應該扣款或撥款多少金額到投資人的帳戶，如同一開始所提到的，這些記錄不能隨著時間過去而消失，因此，一個夠大的資料庫是必要的，但是只有空間夠大是不足的！因為我們也需要一個穩定的資料庫來避免資料的遺失，定期的備份其實十分的重要，這個概念不僅僅是對於金融系統，在任何的系統上，備份的觀念都是不可或缺的。而Oracle的資料備份與還原比較複雜，但也十分完整，同時Oracle也相當重視備份與還原這一個部份，因此Oracle在導資料的功能上提供了更強大的能力，使用者可以依照條件把各種文本文件導入DB，同時也可以將DB中的資料導成各種不同的格式檔，方便使用者做操作。 以一個專案的開發，除非必須安裝的元件，其餘我們通常只能建議客戶使用，客戶所使用的Server可能在Linux或是Windows的作業系統上，尤其當Linux系統屬於開放式且功能應用上比較自由，很多時候不一定是使用較親切的Windows系統，而Oracle擁有良好的開放性，可以運行在目前所有主流的系統上，不僅限定於Windows的作業系統上。 基於以上種種，選擇Oracle作為資料庫的軟體，在金融系統上將有很大的益處，下面將開始針對Oracle做簡單的介紹。 Oracle 11g Oracle11g為目前最新版本，此版本中增加了上百項的新功能，並花費大量的人力與長時間的測試，讓使用者可以更輕鬆的管理資料並且可在低成本的儲存設備和伺服器所組成的網格上具備更卓越的性能、更高的調適性、可用性、安全性與可管理性。 Oracle 11g所提供的新特性有許多，依照使用上可以做個簡單分類。 針對資料庫管理做的優化，如：

• Database Replay：捕捉整個資料庫的負載，並且可以傳送到備份資料庫中創見的測試資料集，來重現測試系統優化後的效果。
• Automatic Diagnostic Repository (ADR)：當偵測到重要錯誤時，會自動建立一個incident，並且取得與此事件相關訊息，接著進行資料庫的檢查並通知DBA。
• 其他針對資料庫管理所做的優化還有Auto SQL Tuning、Auto Memory Tuning、Access Advise、Resource Manager等。

針對PL/SQL部分的改進：

• Result Set Caching：這個特性提升了很多程序的性能，因為我們可以將結果暫存，藉以提升查詢的性能，同時因為結果是被獨立暫存，並不會受其它語句的影響，可同時保證其完整性。
• Read-only Table：在11g中提供了唯讀表，不需要像以前透過語法來對table做唯讀控制。
• Improve DBMS_SQL Package：可接受大於32k的CLOB資料，同時能支持使用者自定義的類型。
• 其它改進的部分還有，增加了simple integer的Type、增加continue的關鍵字、Intra-Unit inlining、Compound trigger、PL/SQL Warning等。

Oracle在此版做了相當大的變更，其中最重要的莫過於連接上目前雲端服務的概念，利用多台機器去提升效能；除此之外，也同時提供DBA更強大的輔助工具，讓DBA擁有更大的權利與工具對資料庫進行調教並且更容易發現問題與處理。 Oracle 11g可以從Oracle的網站進行下載，目前提供的最新版本是11gR2，如果要使用Visual Studio，則還需下載ODAC with Oracle Developer Tools for Visual Studio，同時也可以從Oracle網站去下載SQL Develop開發工具。 Oracle : http://www.oracle.com/technetwork/database/enterprise-edition/downloads/index.html ODT : http://www.oracle.com/technetwork/database/windows/downloads/index-101290.html 安裝完之後，可以藉由[開始]>[所有程式]>[Oracle – OraClient11g_home1]>[應用程式開發]>[SQL Developer] 來開啟Oracle，而第一次開啟則需指定Java路徑。 開啟Oracle以後，其畫面如下： 我們可以在連線處點選右鍵來建立一個新的連線。 將欄位都填入以後，可先用測試來檢查連線。 連線成功後可以見到以下的畫面，有連線的資料庫，會出現類似插頭的符號，同時我們可以點開資料庫，來觀察此資料庫擁有哪些資訊。 我們可以利用右邊的指令畫面，使用語法去針對資料庫進行各式操作，同時也可以開啟兩個以上的連線，來進行比較的動作。 一、表格(Table)： 即為此資料庫中所定義的各種表格，裡面將可存放各式的資料，我們可以直接在表格上點選右鍵去新建一個表格，並且針對此表格中該有的資訊進行設定，每個表格中會有各種不同欄位，我們可以去設定各種不同欄位的型態，設定哪些欄位為Primary Key，各個欄位的預設值，以及設定此張表格的索引來加速我們搜尋；我們同時可以觀察此張表格所擁有的資料，並且針對想要得到的資訊進行過濾，這個功能在我們測試語法時會得到很大的幫助；若想了解Oracle的語法，同時也有一個SQL的Tab可以觀察建立此張Table的語法。 二、視觀表(View)： View是基於一個表格或是多個表格集合的一張邏輯表，本身不包含資料，但是我們可以利用View對表格的資料進行查詢與修改，在運用上，我們可以利用View將表格中所需的資料與欄位進行第一次的篩選，如此一來，在之後的使用上，即可直接從View中撈取資料或進行其它操作(例如先針對表格中某些需要的欄位做Group By與Sum)，以減少資料量並可簡化查詢，加快處理速度。 所以View的優點在於，可以從多個表格中取得資料並且簡化使用者的語法，亦可從單一表格中針對不同需求選取不同資訊產生多個視觀表，同時，利用視觀表可以維持表格中資料的獨立性，避免資料發生非預期變動。 三、索引(Index)： 利用建立索引，可以加快資料的搜尋、減少I/O的操作、消除硬碟儲存的排序，當一個表格中經過多次的新增與刪除，造成表格儲存的資料區塊分散時，就可以利用建立索引的方式，來加快資料的搜尋，同時在一張表格中，允許建立多個索引，不同情況下可使用不同的索引。當語法未指定到索引時，Oracle會預設使用被執行次數最多的索引。 四、程序(Stored Procedure)： 如果想要在Oracle中使用Stored Procedure，需要先取得建立與執行Stored Procedure的權限，所以DBA必須先賦予使用者有使用Stored Procedures的權利，使用Stored Procedure的優點在於可以減少程式與資料庫之間進行頻繁的連線與資料的往來，加快使用上的效能，如果有需要重複的針對資料進行讀寫，則使用Stored Procedure將可獲得極大的效益。 Stored Procedure中可以使用的方法更為多樣，可以使用如同高接程式語言的語法，同時也可以使用Temp Table去暫存資料，或著藉由Cursor去從不同表格中讀取資料，最終再將撈出的資料結合在一起，一個Stored Procedure所擁有的功能與效益將視使用者的操作來決定。 以下是利用Temp Table的例子： 藉由宣告一個暫存的表格將資料填充進去，同時可以針對已填充進去的資料進行操作，如：與別的表格的欄位資料進行相加或相減的操作，最後再從暫存的表格將結果輸出，比起使用來回多次的資料庫讀取，程式只需要呼叫此程序，即可完成操作，效能上即可有很大的提升。 下列是使用高接程式語言判斷並去串接語法的例子： 在此例子中，宣告一個字串去串接語法，由於在Oracle資料庫中判斷某欄位is null時會去執行Table scan，因此先利用判斷式去判斷傳進去的條件是否有空值，若非空值則為篩選的條件，為空值則直接不加入此條件，以此來取代判斷是否為空值的語法，最後再執行串接起來的語法，因為減少了許多條件的is null的判斷，可以大幅增加處理的速度。另外由此可知，一個Stored Procedure是可以使用類似高階語言的語法來進行撰寫(如：迴圈)，不只是限定Stored Procedure只能使用基本的SQL語法。 五、函數(Function)： 與程序類似，差異點為：Function須指定Return Value、Function的Argument Mode只有用In、Function只能用位置表示法。 六、套裝程式(Packages)： 根據Procedure與Function所在的位置有不同的有效範圍，分為standalone procedure(function)：不包含於任何的Package或是subprogram中，屬於schema level、packaged subprogram：定義於Package中，由Package進行區隔、local subprogram：定義於subprogram中或是PL/SQL程式區段中，定義於此段的程式無法由外界去呼叫，類似於Inner Class。 另外，Oracle所儲存的資料量非常的大，當有需要針對龐大資料集進行查詢或操作時也需特別注意使用的語法，盡可能避免使用整張的Table Scan，同時，若資料庫中，單一表格有數以百萬筆的資料，欲針對其中某些條件進行加總或其餘操作時，預先使用subquery將需要的資料撈出，再進行加總的動作也可增加效能，其餘還有當兩張表格資料量較大時使用Exist效能會優於使用In，以及使用Trim的速度會慢於使用RPad將字串補齊的方式。 Oracle的效能受使用者所使用的方式影響很大，有很多的方式都可以達到預期的效果，如：where A.BranchID in (Select BranchID From TableB where 條件) 的語句也可以使用Join TableB on條件的方式達到。因此，只有熟知Oracle的語法，才能善用Oracle所提供的各式功能。 總結 金融系統的資料量雖然不至於到幾百GB甚至是TB，但是金融系統的類別較多，例如：光以投資人來說，其可能是國內或國外投資人，使用的可能是護照、身分證、駕照的號碼，而國外投資人的國籍可能來自世界各地等，這就會需要多張表格進行記錄，分類的好處是分開查詢時可以一目了然，但是要全部顯示出來時，就會需要多個表格一起Join；以帳務來說則更複雜，因為可能投資人有做融資與融券或者是不同的商業行為，而當要顯示投資人所做的完整記錄時，就需要串起多個相關表格，並注意Primary Key的使用以避免串出重複資料，同時，執行多次Join也需考慮表格的大小所造成效能的問題，而這個部份就需要開發時注意表格架構與語法的使用。 所以，雖然Oracle提供了許多強大與便利的功能，但是最重要的還是須具備使用的經驗，熟知Oracle的使用方式才可以從中得到最大的利益。以開發的使用上來講，除了上面所述以外，Oracle也提供了許多的輔助工具。例如：當我們完成一個語法，我們可以利用F6執行來觀察每一句語法的效能，觀察是否因為有Table Scan而造成效能低落，就可以針對那個地方進行修正的動作。而以DBA來說，Oracle也提供了詳盡的錯誤描述，並提供DBA許多工具去找出問題所在，以快速進行修復，減少資料庫失效時造成的損失。 除了強化使用上的許多功能以外，目前Oracle最重要的一個發展技術，則是跟上雲端腳步。在資料量以及計算需求都爆增的時代，資料庫也會面臨到數據傳輸上的問題，如何與雲端概念接軌，來處理大量資料並加速資料傳遞，也是其發展重心。雖然Oracle的操作與使用上較複雜，需要較多的時間去深入了解，才可以從Oracle中取得一定的優勢，但對於未來性來說，學習並使用Oracle並不失為，對於未來龐大資料量的一種較佳的解決方案。 參考資料 Oracle Oracle資料庫的優點 Oracle 11g 新特性介紹 [Oracle]開發&使用Stored Procedures