如何編寫高效健壯的ORACLE存儲過程

【摘要】在數據庫維護過程中，存儲過程的編寫的執行是必不可少的技能，本文作者結合自已的工作，講述了如何編寫高效健壯的ORACLE存儲過程。而這些技術經常是一般的數據庫維護人員所忽略的，因此對于提高維護技能，增加維護經驗有很大的益處。

【關鍵詞】oracle顯式游標隱式游標綁定變量

做為一個應用系統維護人員，和數據庫打交道是必不可少的。當發現表中的數據有誤時，我們可以通過SQL語句去查詢修改，但是當我們要修改一批數據，或者要對在前臺增加某項處理功能的時候，我們就要編寫存儲過程，通過直接后臺執行或前臺功能調用的方法處理批量數據。企業級的數據庫都是比較繁忙的，所以如何編寫高效健壯的ORACLE存儲過程就擺在了維護人員的面前。

一、定義嚴格的數據類型

Oracle的表中對數據類型有嚴格的定義，對于表中的列一般要定義：列的數據類型、列的最大長度和適當的精度、對于可接受值的約束。在存儲過程中對于變量的定義沒有以上的嚴格要求，但是過程中的變量一般都對應表中的列值，所以如果能夠將兩者關聯定義對于過程健壯性是很好的保障。

存儲過程中大多數的變量直接與數據表的列掛鉤，% TYPE可以將這種關系在代碼中清晰的體現出來。如下面二個變量的定義都存儲tf_f_user_customer.cust_name：

V_custname varchar2（200）；

V_custname tf_f_user_customer.cust_name%type；

對于第一種定義，如果表中的cust_name長度大于200個字符，那么執行時就會報錯，需要重新修改V_custname的定義，但是對于第二種定義，由于變量和字段類型相關聯，所以能夠存儲到字段的值必然也能夠存儲到變量里，且修改了字段的類型，變量的類型也會隨之更改，不用人工再做調整，減輕了維護的工作量。

和%TYPE類似的還有一個%ROWTYPE屬性，它是將一個記錄型的變量和一個表的所有列關聯。如定義： v_trade tf_b_trade%rowtype；這樣我們可以不必一個一個的定義每一個字段，可以將一個表中的一行記錄取出后放到v_trade變量中統一處理，這種方法對于存儲過程的編寫和后期的維護都是非常方便的。

保證所有適當的變量都是強類型的可以獲得性能上的好處，雖然ORACLE在數據處理方面對于開發者的考慮是很周到的，如果將字符串賦給數值型變量，ORACLE會嘗試將其轉換成數據并完成賦值操作，但是為對系統資源的消耗是很大的。實驗結果表明96%的執行時間消耗在了類型轉換上。

二、顯式游標和隱式游標

我們在編寫存儲過程的時候往往會用到顯式游標，顯式游標我們可以完全控制，且在一些教科書上都有一個結論：顯式游標的性能是最好的。但是實驗的結果表明，隱式游標在代碼上更簡潔，且在大多數的情況下性能等同于等價的顯式游標，甚至更強。

我們在存儲過程中使用SELECT INTO語句（即隱式游標）對單行數據進行讀取，有3種可能的結果：（1）結果集中只含有一行，且SELECT成功。（2）結果集中不包含任何行，引發NO_DATA_FOUND異常。（3）結果集中含有兩行或更多行，引發TOO_MANY_ROW異常。

有些人可能認為了發現第三個錯誤，該語句至少要執行兩次，然而可以編寫顯示游標，控制代碼只讀取一次，所以顯式游標的性能更好。但是在ORACLE7.1以后的版本引入了預讀的概念，一次讀取兩行的數據，所以NO_DATA_FOUND異常和TOO_MANY_ROW異常都是由單次讀取決定，且隱式游標編寫更簡潔，可讀性更好。實驗表明，單行讀取的隱式游標要比顯式游標效率提高20%。

在FOR循環中使用隱式游標的語法來讀取行集也是很有幫助的。可以在FOR循環中直接使用SQL語句，這要做可以不需要掃描代碼的聲明部分來檢查定義。

三、綁定變量

在oracle中，對于一個提交的sql語句，存在兩種可選的解析過程，一種叫做硬解析，一種叫做軟解析。不使用綁定變量，對于執行頻度非常高的sql，每次都會進行硬解析，這將帶來很大的危害，而使得oracle能夠重復利用執行計劃的方法就是采用綁定變量。綁定變量的實質就是用變量替代sql語句中的常量。通過綁定變量使得每次提交的sql語句都完全一樣。

結語：以上我們講述了如果提高存儲過程性能和便于后期維護的方法，當然還有好多的方法在這里并沒有提到，我們只有綜合運用這些方法才能編寫出高效健壯的存儲過程。