PL/SQL最差實踐

1. 超長的PL/SQL代碼

           影響：可維護性，效能

           癥狀：

            在複雜的公司專屬應用程式中，存在動輒成百上千行的預存程序或上萬行的包。

        為什麼是最差：

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

        解決之道：

            PL/SQL代碼在執行前會被載入到shared pool中，shared pool以位元組為單位，UNIX下為64K，案頭環境下為32K，可以通過查詢資料字典USER_OBJECT_SIZE的PARSED_SIZE欄位查看對象大小。對於較大的包，應採用拆包策略，抽取複用部分，減少重複代碼；對於較大的預存程序，應將預存程序組織到包中，易於管理；對於較大的匿名塊，應將匿名塊重新定義成子過程儲存在資料庫中。

        2. 脫離控制的全域變數

        影響：可維護性

        癥狀：在包中使用了全域變數，在多個位置對全域變數進行操作。

        CREATE OR REPLACE PACKAGE BODY PKG_TEST IS

        GN_全域變數 NUMBER(12, 2);

        PROCEDURE 過程A IS

        BEGIN

        GN_全域變數:=1;

        END;

        PROCEDURE 過程B IS

        BEGIN

        GN_全域變數:=2; -- 這裡對全域變數進行了另外的操作

        END;

        為什麼是最差：

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

        解決之道：

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

        3. PL/SQL中嵌入複雜SQL語句

        影響：可維護性

        癥狀：

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

        ...

        PROCEDURE 過程A IS

        BEGIN

        UPDATE T_A SET COL1 = 10;

        END;

        PROCEDURE 過程B IS

        BEGIN

        DELETE FROM T_A WHERE COL1=10;

        END;

        ...

        為什麼是最差：

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

        ? 在多個位置對錶進行訪問，不利於SQL最佳化

        解決之道：

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

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

        4. “異常”的異常處理

        影響：可維護性，健壯性

        癥狀：我們來看下面的代碼：

        PROCEDURE 過程A(錯誤碼 out varchar2,錯誤資訊 out varchar2) IS

        BEGIN

        ...

        UPDATE T_A SET COL1 = 10;

        SELECT ... FROM T_A WHERE ...;

        DELETE FROM T_A WHERE COL1 = 20;

        ...

        EXCEPTION

        WHEN OTHERS THEN

        ...

        END;

        為什麼是最差：

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

        解決之道：

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

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

        ? 自訂完整的異常資訊，異常資訊中包含異常發生時的情境。5. 固定的變數長度和變數類型

        影響：可維護性

        癥狀：當聲明基於欄位類型的變數時，尤其是varchar2類型，直接使用固定長度聲明。

        為什麼是最差：

        ? 這種硬式編碼變數大小很可能與資料庫中實際大小不符

        ? 如果欄位的類型、大小等發生變化，還需要到PL/SQL中調整變數

        解決之道：

        使用%Type聲明與欄位類型相關的變數。

        6. 不做單元測試

        影響：健壯性

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

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

        解決之道：

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

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

        影響：可維護性

        癥狀：我們看下面的代碼：

        方法1：

        V_sex:=’1’; -- 男

        方法2：

        CONST_MALE CONSTANT VARCHAR2(1) := '1'; -- 定義常量 男

        V_sex:=CONST_MALE;

        為什麼是最差：

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

        ? 當其他項目男性性別定義修改為“2”時，採用方法1編碼的程式需要仔細尋找每一段代碼，容易產生錯誤，而採用方法2編碼的程式只修改常量定義即可。

        解決之道：

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

        8. 不對PL/SQL對象進行組態管理

        影響：可維護性

        癥狀：PL/SQL對象（package、package body、trigger、procedure、type、type body、函數等）的代碼沒有使用組態管理工具進行維護和更新。

        為什麼是最差：

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

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

        ? Oracle對象不能追溯既往，資料庫中只能儲存最新

        解決之道：

        ? 規範開發過程，以組態管理工具上的PL/SQL代碼為最新。

        ? 使用第三方外掛程式減少同步工作量，如PL/SQL Developer下的VCS版本控制外掛程式。

        9. IF … ELSE …的壞味道

        影響：可維護性

        癥狀：大量使用IF … ELSE

        為什麼是最差：

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

        解決之道：

        ? 使用Oracle資料庫的繼承特性，通過type實現對象的繼承，利用策略模式封裝差異，對外提供統一的調用介面

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

 

        10. 在非自治事務中控制事務

          影響：資料一致性

          癥狀：

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

        PROCEDURE 過程A(錯誤碼 out varchar2,錯誤資訊 out varchar2) IS

        BEGIN

        ...

        SAVEPOINT A;

        UPDATE T_A SET COL1 = 10;

        COMMIT;

        DELETE FROM T_A WHERE COL1 = 20;

        ROLLBACK TO A;
        ...

        EXCEPTION

        WHEN OTHERS THEN

        ...

        END;

        為什麼是最差：

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

        解決之道：

        ? 由調用者決定何時提交或復原事務。

        ? 對於需要特殊交易管理的過程如記載日誌，使用自治事務。

        11. 不使用綁定變數

        影響：效能

        癥狀：直接使用值而不使用綁定變數進行查詢。尤其是在拼字sql的程式中，這種情況更突出。

        為什麼是最差：

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

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

        12. 慎用ROWNUM=1

        影響：可維護性、資料一致性

        癥狀：在讀取資料時，有時只需要取一行，這時WHERE條件中就會用到ROWNUM=1。

        為什麼是最差：

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

        解決之道：瞭解要查詢資料的含義，使用其他條件限制結果集。

        13. 靈活的動態SQL

        影響：可維護性、效能

        癥狀：EXECUTE IMMEDIATE ‘SELECT A FROM TAB1’ INTO v_a;

        為什麼是最差：

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

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

        14. 對ROWID進行訪問

        影響：資料一致性

        癥狀：使用ROWID作為資料更新、刪除的WHERE條件

        為什麼是最差：

            ROWID屬於Oracle底層儲存結構，會隨著資料的遷移、匯入、匯出發生變化，而商務邏輯則不應依賴底層儲存結構。

        解決之道：使用主鍵進行資料操作。

本文轉自:http://oracle.chinaitlab.com/PLSQL/754539_2.html