常用存儲過程技巧總結(jié)培訓(xùn)

存儲過程技巧總結(jié)李攀2011-01-24存儲過程優(yōu)點(diǎn)開發(fā)速度比較快，直接通過SQL語句對數(shù)據(jù)庫操作部署簡單，尤其是在處理定時JOB時，通過數(shù)據(jù)庫即可方便處理適合處理較復(fù)雜且經(jīng)常變化的數(shù)據(jù)庫邏輯事務(wù)控制方便，良好的異常捕獲機(jī)制學(xué)習(xí)門檻較低，只要會寫SQL，掌握基本的技巧，一天即可學(xué)會常用的操作。存儲過程的缺點(diǎn)會造成數(shù)據(jù)庫的壓力過大過多的存儲過程，在版本庫的管理中比較麻煩，每次都需要從PL/SQL中導(dǎo)出或保存，哪些有改動哪些無改動需要同步至版本庫數(shù)據(jù)緩存不方便，過多頻繁的查詢會降低程序運(yùn)行速度，譬如接口數(shù)據(jù)同步最好不要用存儲過程對函數(shù)的支持不如C++或JAVA豐富，開發(fā)含有復(fù)雜的函數(shù)邏輯較差避免使用特殊的函數(shù)，部分10g函數(shù)在9I上不支持結(jié)構(gòu)及寫法（個人習(xí)慣）嵌套層次不宜過深，一般2至3層就差不多，再多時理解比較的難，常用的結(jié)構(gòu)為：第一層游標(biāo)循環(huán)；第二層負(fù)責(zé)單個對象處理，事務(wù)在此層進(jìn)行控制；第三層主要是寫一些共通的代碼，減少重復(fù)，一般由第二層調(diào)用第三層函數(shù)或存儲過程對于常量的定義，建議寫在PACKAGE中，存儲過程中不宜出現(xiàn)某字段與一數(shù)值或字符串之間比較，如V_DATATYPE=1時，應(yīng)該改寫為V_DATATYPE=PK_FLAG.YEAR結(jié)構(gòu)及寫法（個人習(xí)慣）第二層控制事務(wù)時，在內(nèi)部嵌套一個最大異常捕獲，同時加上ROLLBACK，在異常上方最后一行或給結(jié)果賦值時，添加了COMMIT，避免任何時候未提交或回滾導(dǎo)致鎖表從存儲過程命名上即可看出其功能，第一層存儲過程與第二層存儲過程命名盡量類似，方便查找存儲過程內(nèi)部所有字母均大寫，變量以“_”作為分隔符，從命名上要能看出變量的意義，變量名稱嚴(yán)禁按照縮寫方式，如母線分段，V_MXFD結(jié)構(gòu)及寫法（個人習(xí)慣）無特殊情況，盡量避免采用動態(tài)SQL，這種寫法編譯時無法發(fā)現(xiàn)只有在程序運(yùn)行時才會被發(fā)現(xiàn)，而且在處理單引號、日期時極易出錯在存儲過程頭部對參數(shù)予以描述，尤其是時間類型的，定義好格式，描述存儲過程的主要功能以及含有特殊業(yè)務(wù)處理程序內(nèi)部邏輯添加必要的注釋，最好采用1、2、3、1.1、1.2數(shù)字標(biāo)簽出來，比較清晰代碼對齊，方便識別哪些代碼屬于一個BLOCK游標(biāo)顯示游標(biāo)OPENCURSORFOR….LOOP

FETCHCURSORINTOV_DEVICEID;

EXITWHENCUR_MODELS%NOTFOUND;……;

ENDLOOP;

CLOSECUR_MODELS;適用于游標(biāo)變量引用地方較多，注意關(guān)閉游標(biāo)。游標(biāo)游標(biāo)forcin(select*FROM…)

loop

….

endloop;直接通過C.ID即可引用，方便，不需要定義變量，不需要考慮關(guān)聯(lián)游標(biāo)SELECT語句中各列盡量采用別名，力求像變量可以看出其用途異常捕獲NO_DATA_FOUNDTO_MANY_ROWSDUP_VAL_ON_INDEXOTHERSDBLINK對于通過DBLINK查詢的SQL語句，在結(jié)束時仍然需要執(zhí)行COMMIT或ROLLBACK，因?yàn)槠鋾跀?shù)據(jù)庫中保留一個緩沖區(qū)效率對于復(fù)雜的SQL