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

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

【關(guān)鍵詞】oracle顯式游標(biāo)隱式游標(biāo)綁定變量

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

一、定義嚴格的數(shù)據(jù)類型

Oracle的表中對數(shù)據(jù)類型有嚴格的定義，對于表中的列一般要定義：列的數(shù)據(jù)類型、列的最大長度和適當(dāng)?shù)木取τ诳山邮苤档募s束。在存儲過程中對于變量的定義沒有以上的嚴格要求，但是過程中的變量一般都對應(yīng)表中的列值，所以如果能夠?qū)烧哧P(guān)聯(lián)定義對于過程健壯性是很好的保障。

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

V_custname varchar2（200）；

V_custname tf_f_user_customer.cust_name%type；

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

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

保證所有適當(dāng)?shù)淖兞慷际菑婎愋偷目梢垣@得性能上的好處，雖然ORACLE在數(shù)據(jù)處理方面對于開發(fā)者的考慮是很周到的，如果將字符串賦給數(shù)值型變量，ORACLE會嘗試將其轉(zhuǎn)換成數(shù)據(jù)并完成賦值操作，但是為對系統(tǒng)資源的消耗是很大的。實驗結(jié)果表明96%的執(zhí)行時間消耗在了類型轉(zhuǎn)換上。

二、顯式游標(biāo)和隱式游標(biāo)

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

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

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

在FOR循環(huán)中使用隱式游標(biāo)的語法來讀取行集也是很有幫助的?？梢栽贔OR循環(huán)中直接使用SQL語句，這要做可以不需要掃描代碼的聲明部分來檢查定義。

三、綁定變量

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

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