PL/SQL最差實踐

　　 超長的PL/SQL代碼

　　影響可維護性性能

　　症狀

　　在復雜的企業應用中存在動辄成百上千行的存儲過程或上萬行的包

　　為什麼是最差

　　太長的PL/SQL代碼不利於閱讀第三方工具在調試時也會出現代碼行混亂等問題PL/SQL存儲對象（存儲過程包函數觸發器等）行數上限約為行但實際工作中當包大小超過行就會出現調試問題

　　解決之道

　　PL/SQL代碼在執行前會被加載到shared pool中shared pool以字節為單位UNIX下為K桌面環境下為K可以通過查詢數據字典USER_OBJECT_SIZE的PARSED_SIZE字段查看對象大小對於較大的包應采用拆包策略抽取復用部分減少重復代碼對於較大的存儲過程應將存儲過程組織到包中易於管理對於較大的匿名塊應將匿名塊重新定義成子過程保存在數據庫中

　　 脫離控制的全局變量

　　影響可維護性

　　症狀在包中使用了全局變量在多個位置對全局變量進行操作

　　CREATE OR REPLACE PACKAGE BODY PKG_TEST IS

　　GN_全局變量 NUMBER( );

　　PROCEDURE 過程A IS

　　BEGIN

　　GN_全局變量:=;

　　END;

　　PROCEDURE 過程B IS

　　BEGIN

　　GN_全局變量:=; 這裡對全局變量進行了另外的操作

　　END;

　　為什麼是最差

　　全局變量可以在整個包范圍內被訪問到因此對全局變量的跟蹤和調試會比較困難如果變量是在package中定義的變量還可以被其他包訪問這將會更為危險

　　解決之道

　　減少或取締全局變量的使用對於要在過程間交互的變量通過參數傳遞來實現如果必須使用全局變量應對全局變量進行get/set函數封裝規范對全局變量的訪問

　　 PL/SQL中嵌入復雜SQL語句

　　影響可維護性

　　症狀

　　在PL/SQL代碼中嵌入SQL語句如

　　

　　PROCEDURE 過程A IS

　　BEGIN

　　UPDATE T_A SET COL = ;

　　END;

　　PROCEDURE 過程B IS

　　BEGIN

　　DELETE FROM T_A WHERE COL=;

　　END;

　　

　　為什麼是最差

　　? PL/SQL代碼中嵌入SQL語句使得代碼含義變得難於閱讀和理解

　　? 在多個位置對表進行訪問不利於SQL優化

　　解決之道

　　? 將分散SQL語句進行封裝例如上例中的刪除語句可以封裝為prc_刪除T_A()過程參數為T_A的type類型對T_A的刪除操作都委托此過程處理當T_A表增加或刪除字段時主要的變化都集中在這些過程中對其他邏輯影響較少

　　? 對SQL的優化集中在封裝的過程中

　　 異常的異常處理

　　影響可維護性健壯性

　　症狀我們來看下面的代碼

　　PROCEDURE 過程A(錯誤代碼 out varchar錯誤信息 out varchar) IS

　　BEGIN

　　

　　UPDATE T_A SET COL = ;

　　SELECT FROM T_A WHERE ;

　　DELETE FROM T_A WHERE COL = ;

　　

　　EXCEPTION

　　WHEN OTHERS THEN

　　

　　END;

　　為什麼是最差

　　整個過程只有一個WHEN OTHERS 的異常段示例中的三個語句發生的異常只能被最外層捕捉無法區分發生異常的種類和位置

　　解決之道

　　? 不使用WHEN OTHERS捕捉所有異常例如不應該捕捉NO_DATA_FOUND異常使用專用的Exception來捕捉特定的異常

　　? 聲明自己的異常處理機制處理與業務相關的異常將業務異常與系統運行期異常分開處理

　　? 自定義完整的異常信息異常信息中包含異常發生時的場景

　　 固定的變量長度和變量類型

　　影響可維護性

　　症狀當聲明基於字段類型的變量時尤其是varchar類型直接使用固定長度聲明

　　為什麼是最差

　　? 這種硬編碼的變量大小很可能與數據庫中實際大小不符

　　? 如果字段的類型大小等發生變化還需要到PL/SQL中調整變量

　　解決之道

　　使用%Type聲明與字段類型相關的變量

　　 不做單元測試

　　影響健壯性

　　症狀PL/SQL代碼中蘊含大量的業務邏輯這些邏輯編寫完畢後沒有提供合適的單元測試用例用於驗證

　　為什麼是最差 不做單元測試的危害這裡就不再廢話了

　　解決之道

　　PL/SQL並沒有提供諸如JUnit之類易用的單元測試工具現在有一些開源工具可以使用使用utPLSQL()工具進行單元測試或DBUnit進行二次開發滿足不同應用的需要

　　 使用代碼值而不使用代碼名稱

　　影響可維護性

　　症狀我們看下面的代碼

　　方法

　　V_sex:=; 男

　　方法

　　CONST_MALE CONSTANT VARCHAR() := ; 定義常量 男

　　V_sex:=CONST_MALE;

　　為什麼是最差

　　? 從例子中可以看出同樣是使用性別方法是直接使用代碼值方法是使用常量看上去似乎方法要比方法麻煩一些但方法比方法更為直觀代碼的可讀性也更好代碼的閱讀者不需要關注代表什麼含義

　　? 當其他項目男性性別定義修改為時采用方法編碼的程序需要仔細查找每一段代碼容易產生錯誤而采用方法編碼的程序只修改常量定義即可

　　解決之道

　　將常量定義放入到公共的代碼包中供其他程序共享所有涉及到代碼值的比較引用等都必須使用常量名而不能直接書寫代碼值對於一些復雜的代碼值間的關系可以進一步封裝以函數的方式提供調用

　　 不對PL/SQL對象進行配置管理

　　影響可維護性

　　症狀PL/SQL對象（packagepackage bodytriggerproceduretypetype body函數等）的代碼沒有使用配置管理工具進行維護和更新

　　為什麼是最差

　　因為Oracle內部結構的差異對象的管理具有一定的難度尤其是在並行開發的情況下

　　? 對象職責劃分不清造成多人同時修改一個對象在編譯時如果後來者沒有獲取最新的代碼會造成前一個開發人員修改的代碼被覆蓋

　　? Oracle對象不能追溯既往數據庫中只能保存最新

　　解決之道

　　? 規范開發過程以配置管理工具上的PL/SQL代碼為最新

　　? 使用第三方插件減少同步工作量如PL/SQL Developer下的VCS版本控制插件

　　 IF … ELSE …的壞味道

　　影響可維護性

　　症狀大量使用IF … ELSE

　　為什麼是最差

　　大量存在IF/ELSE造成代碼邏輯混亂不易修改無論是PL/SQL還是其他編程語言這種代碼都已經飄著bad smell了

　　解決之道

　　? 使用Oracle數據庫的繼承特性通過type實現對象的繼承利用策略模式封裝差異對外提供統一的調用接口

　　? 將頻繁使用的IF/ELSE代碼重構為單獨的過程或函數供其他代碼復用

　　 在非自治事務中控制事務

　　影響數據一致性

　　症狀

　　在PL/SQL非自治事務代碼中控制事務例如

　　PROCEDURE 過程A(錯誤代碼 out varchar錯誤信息 out varchar) IS

　　BEGIN

　　

　　SAVEPOINT A;

　　UPDATE T_A SET COL = ;

　　COMMIT;

　　DELETE FROM T_A WHERE COL = ;

　　ROLLBACK TO A;
       

　　EXCEPTION

　　WHEN OTHERS THEN

　　

　　END;

　　為什麼是最差

　　這種行為是我認為最差實踐中危害最大的一種隨處可見的事務控制代碼會造成數據不一致引發的問題難於跟蹤和調試

　　解決之道

　　? 由調用者決定何時提交或回滾事務

　　? 對於需要特殊事務管理的過程如記載日志使用自治事務

　　 不使用綁定變量

　　影響性能

　　症狀直接使用值而不使用綁定變量進行查詢尤其是在拼寫sql的程序中這種情況更突出

　　為什麼是最差

　　這是一個常見問題當代碼中大量充斥固定的代碼值時數據庫引擎每次都需要重新解析不能使用既有的執行計劃

　　解決之道對於這種經常執行的語句使用綁定變量而非實際參數值執行

　　 慎用ROWNUM=

　　影響可維護性數據一致性

　　症狀在讀取數據時有時只需要取一行這時WHERE條件中就會用到ROWNUM=

　　為什麼是最差

　　之所以將這個實踐評成最差是因為筆者在實際工作中曾經遇到過這類問題跟蹤和調試都很困難ROWNUM本身的處理順序是在ORDER BY 之前所以當ROWNUM=時產生的結果很可能是隨機的

　　解決之道了解要查詢數據的含義使用其他條件限制結果集

　　 靈活的動態SQL

　　影響可維護性性能

　　症狀EXECUTE IMMEDIATE SELECT A FROM TAB INTO v_a;

　　為什麼是最差

　　動態SQL失去了編譯期檢查能力將發生問題的可能性推遲到運行期動態SQL也不利於優化因為只有在運行期才能得到完整的SQL語句

　　解決之道盡量避免使用動態SQL對於易變的業務邏輯可以抽取到中間層實現

　　 對ROWID進行訪問

　　影響數據一致性

　　症狀使用ROWID作為數據更新刪除的WHERE條件

　　為什麼是最差

　　ROWID屬於Oracle底層存儲結構會隨著數據的遷移導入導出發生變化而業務邏輯則不應依賴底層存儲結構

　　解決之道使用主鍵進行數據操作

From:http://tw.wingwit.com/Article/program/Oracle/201311/17942.html