如何編寫高效健壯的ORACLE存儲(chǔ)過程

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

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

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

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

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

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

V_custname varchar2（200）；

V_custname tf_f_user_customer.cust_name%type；

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

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

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

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

我們?cè)诰帉懘鎯?chǔ)過程的時(shí)候往往會(huì)用到顯式游標(biāo)，顯式游標(biāo)我們可以完全控制，且在一些教科書上都有一個(gè)結(jié)論：顯式游標(biāo)的性能是最好的。但是實(shí)驗(yàn)的結(jié)果表明，隱式游標(biāo)在代碼上更簡潔，且在大多數(shù)的情況下性能等同于等價(jià)的顯式游標(biāo)，甚至更強(qiáng)。

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

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

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

三、綁定變量

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

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