pl/sql reference綜述

pl/sql reference綜述

plsql user's guide and reference 已經讀完一個多月了，再使用相關的功能，發現很多都記不清了。有必要review一遍。

pl/sql是我們在進行程式開發及後期維護、管理等工作中的一把利器。其與sql聯絡緊密，除explain plan外絕大部分sql語句可以在pl/sql中直接使用，授權、建表等DDL操作無法直接呼叫，但可以藉助動態語句解決。
通常來說，處於簡單考慮，使用sql是我們的首選。對於需要批量操作、定時執行、結構複雜需要多步操作等問題，藉助pl/sql則更好。實現功能時不能一味地追求難度大、通用性好，而應該結合應用，在設計開始前從功能、效能、必要性等方面考慮可以剔除很多不必要的需求。

pl/sql相對於sql來說，可以以包、儲存過程等形式儲存在伺服器上，一次解析後再呼叫時可能不需要再解析；為了保證安全性，可以加密儲存；批量資料處理方面，恰當的使用可以每次提取一批資料，而不是sql那樣每次一條，效率較高；由於其特點是描述性語句和麵向物件思想的結合，既簡單有高效。

pl/sql結構分為宣告、執行、異常處理三部分。

宣告變數時，既可以使用sql中的大部分資料型別，也可以使用pl/sql特有的plsql_integer、binary_integer、reference types（ref cursor）等，還可以使用%type、%rowtype。宣告時可以為變數指定預設值和非空約束。
變數的值可以是表示式或字串。
定義type時，自定義幾列、選取表的幾列、其他的type都可以。

執行部分包括待處理的語句塊，可以包含其他pl/sql塊的呼叫（巢狀）。通常來說若事務失敗，整個事務（上次提交後的操作）會被回滾。為了記錄日誌等操作，可以新增自治事務，避免與主處理塊相互影響。
如果在執行部分有巢狀，需要注意變數的作用域和可見性。

在異常處理部分給出資料塊處理中出現異常時的解決方法。既可以使用系統中定義好的異常，也可以使用在宣告部分自定義的異常。

oracle中NULL值的使用應該多留意。當前未指定和NULL的含義是相同的。

前面提到，oracle的資料型別包含了sql的，還有很多特有資料型別。處於對本地化的支援，還有很多本地化的資料型別（如NCHAR、NVARCHAR）。
處理資料時要注意環境設定（字符集）對資料長度的影響，變數間比較或變數與字面值比較時要注意char、nchar等定長型別的末尾空格。語句中最好對變數進行顯式的型別轉換。
在不同版本間運算元據時，特別注意不同版本對資料型別的支援。

對於pl/sql的結構控制，主要是IF、loop、case、語句的使用，goto語句違背結構化設計思想，儘量避免。出於事務完整性考慮，相關聯的操作統一提交，並且注意事務的規模不要太大，以免中間出現問題時難以恢復。

個人認為pl/sql的難點和重點主要體現在集合、物件、大資料量處理三個方面。從官方文件中對這兩方面的介紹篇幅就可見一斑。
oracle有巢狀表（nested tables）、變長資料（varrarys）、索引表（associative arrays|Index-by tables）三種。
三種集合型別區別主要在於上下界、連續性、是否可以作為表列的資料型別使用。
利用集合的exists、count、limit等方法可以將查到的資料進行中間處理（，然後再插入目標表中）。
對於大資料量的表，用簡單的pl/sql語句可能能夠實現功能，但好的效能依賴於更少的步驟、集合的合理使用等。

大資料量處理的核心思想就是分批提取、分配提交。即使是資料量較少，開發人員也需要在可讀性和效能上做出平衡。儘量減少大表的訪問次數、減少資料的查詢量（減少I/O）。
查詢並插入大量資料時，先將資料插入遊標中，在從遊標中分批將資料插入目標表；
對大量資料進行修改時，？？？？？？

 

如果事先不知道變數的名稱、數量等資訊，需要執行時指定，可以採用動態語句。此外，DDL操作在pl/sql中不支援，需要放入動態語句中；為了減少程式碼量、使語句更靈活，也可以使用動態sql。但由於動態語句是在語句執行時才解析的，因此效率會受到影響。

動態語句中不但可以包含語句，還能包含語句塊，例如：EXECUTE IMMEDIATE 'BEGIN dbms_output.put_line(''semicolons''); END;';

oracle的子過程包括儲存過程、函式、巢狀子過程、遞迴子過程等。呼叫其他schema下的物件時，預設是該使用者的許可權。通過authid可以指定使用當前使用者的許可權。

當處理一系列彼此相關或者同類事物時，可以將儲存過程、函式等放入包內，提高了資料的內聚性。同時將細節隱藏，只將部分需要呼叫的過程顯示給使用者，更加安全，效能也更好。
包分為包宣告和包體兩部分，包宣告中應該包含所有使用者需要呼叫物件的宣告，否則無法呼叫。包體中的內容應該有序，編譯時從上到下。有巢狀關係的子過程之間，被呼叫的應該放在前面，否則會由於找不到物件而報錯。
經常使用的dbms_output、dbms_pipe、utl_file、utl_http、dbms_alert等需要掌握。

為了避免系統報錯、在出錯時方便查詢原因，應該儘量捕獲所有的異常、異常的提示資訊滿足應用的需要。異常處理部分是按順序判斷的，when others then的方式捕獲異常放在最後。
系統內部定義的異常直接呼叫就可以，自定義異常需要在宣告部分指定。
“exception_name exception；”，異常處理部分使用raise語句；
pragma exception_init(exception_name,-oracle_error_number);宣告帶錯誤編號的異常。
執行部分可以用自定義異常：raise_application_error(error_number, message[, {TRUE | FALSE}]);（錯誤號範圍是-20000 .. -20999）

在巢狀語句塊中，發生異常時將逐層向外掃描，如果最外層都沒有捕獲到異常，就報錯。
異常應該也是兩類：（個人觀點）
一種是可以預見到或者說程式設計的一部分。比如判斷表示式是否大於零，如果大於，則繼續執行，否則向日志表中插入提示資訊；
另一種是不可預見或者說不希望發生，但又無法避免的。比如動態語句中從遊標獲取資料，取出的資料為空，這時用no_data_found或others等進行補貨並提示。

為了在異常發生後繼續執行後續程式碼，可以將該部分內容放入子過程塊，並在其異常處理部分中放入異常處理。這樣，發生異常後只是該子過程塊結束，主過程塊繼續執行。

還有一點需要注意：宣告部分出現的異常無法補獲，只能報錯。

oralce是將pl/sql警告分為三類，即server、performance、informational
根據需要，將警告的引數設定為ENABLE：ALL|PERFORAMNCE\DISABLE:ALL等

nocopy選項主要是在子過程塊呼叫時傳遞形參而非實際變數
管道化表函式主要用於資料倉儲環境下的多事務處理
使用特性時需要注意其應用場景和限制條件。

隨著物件導向設計的蔓延，oracle也在逐漸優化其object type的功能。
此部分目前沒有實際的應用經驗，因此不詳述。

 

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26451536/viewspace-752212/，如需轉載，請註明出處，否則將追究法律責任。