將PL/SQL代碼封裝在靈巧的包中

將PL/SQL代碼封裝在靈巧的包中

絕大多數基於PL/SQL的應用都是由成千上萬甚至上百萬行程式碼群組成，這裡麵包含了詳細多變的使用者需求。
商業邏輯的實現最初是由預存程序和函數完成，但是開發人員需要考慮將這些過程和函數放在包中維護。

何為包？
包是一組PL/SQL代碼元素（遊標、類型、變數、過程、函數）集合的程式單元。

通常由包聲明（對象聲明）和包體（具體實現）組成。

為什麼要使用包？
1）組織和維護一組功能相關的對象；
2）對外隱藏具體實現；
3）提升效能，這一點要說一下：
當你第一次調用包時，整個包被載入入記憶體。接下來對同一包元素進行調用無需額外的磁碟I/O。
另外，包層級變數可以再會話層級（session-level）緩衝起來，從而降低資料讀取時間。
4）最小化程式單元重編譯
外部程式（沒有定義在包中）僅能調用包聲明中的子程式。如果你改變並重新編譯了包體，那些外部程式
將不會失效。

下面展示一下包的魅力：

1 一個簡單的包：
假設我的employees表定義如下：

SQL> desc employeesName             Type————————————     —————————————EMPLOYEE_ID      NUMBER(38)FIRST_NAME       VARCHAR2(30)LAST_NAME        VARCHAR2(50)

下面我需要定義一個process_employee的過程，返回員工全名（last_name, first_name）以供其他
程式調用。

Code Listing 1: The process_employee procedure

CREATE OR REPLACE PROCEDURE process_employee (   employee_id_in IN employees.employee_id%TYPE)IS   l_fullname VARCHAR2(100);BEGIN   SELECT last_name || ',' || first_name     INTO l_fullname     FROM employees    WHERE employee_id = employee_id_in;    ...END; 

仔細看，這個過程有幾個問題：
1）l_fullname 長度固定為100？
2）l_fullname的運算式固定為 last_name || ‘,’ || first_name？萬一哪天客戶改變主意：
我們想在所有報告和資訊中顯示：first_name【空格】last_name咋辦？如果你在N個過程中都已經
使用了這種結構，那你是不是去一一找出來修改掉？
3）最後一點，我們很有可能在不同的過程中編寫一些重複SQL，這樣會大大降低效率和效能

這個時間，我們需要將這種通用邏輯藏在包中，保證一處維護處處受益：

CREATE OR REPLACE PACKAGE employee_pkg2    AS3        SUBTYPE fullname_t IS VARCHAR2 (100);4     5        FUNCTION fullname (6           last_in  employees.last_name%TYPE,7           first_in  employees.first_name%TYPE)8           RETURN fullname_t;9     10        FUNCTION fullname (11           employee_id_in IN employees.employee_id%TYPE)12           RETURN fullname_t;13    END employee_pkg;

回頭再改寫過程，可以這樣：

CREATE OR REPLACE PROCEDURE process_employee (   employee_id_in IN employees.employee_id%TYPE)IS   l_name employee_pkg.fullname_t;   employee_id_in   employees.employee_id%TYPE := 1;BEGIN   l_name := employee_pkg.fullname (employee_id_in);   ...END;

代碼變整潔了，還有你壓根不需要關心employee_pkg.fullname 如何?！多省心！

來看下包體是如何?的：

CREATE OR REPLACE PACKAGE BODY employee_pkg2    AS3       FUNCTION fullname (4          last_in employees.last_name%TYPE,5          first_in employees.first_name%TYPE6       )7          RETURN fullname_t8       IS9       BEGIN10         RETURN last_in || ', ' || first_in;11      END;12     13      FUNCTION fullname (employee_id_in IN employee.employee_id%TYPE)14         RETURN fullname_t15      IS16         l_fullname fullname_t;17      BEGIN18         SELECT fullname (last_name, first_name) INTO l_fullname19           FROM employees20          WHERE employee_id = employee_id_in;21     22         RETURN l_fullname;23       END;24    END employee_pkg;

這裡用到了函數重載，使得外部過程只需要傳入不同參數即可調用不同版本的函數。
最終都會返回fullname！

2 包層級資料
此類資料由包聲明和包體中全域的variables 和 constants組成。

例如：

CREATE OR REPLACE PACKAGE plsql_limitsIS   c_varchar2_length CONSTANT       PLS_INTEGER := 32767;   g_start_time PLS_INTEGER;END;

當你在一個子程式或匿名塊中聲明一個變數，稱為本地變數，其聲明周期限制在一次子程式調用或匿名塊執行。

而包層級資料是在整個會話期間都會存活。

如果你在包體中定義包資料（變數和常量），該資料同樣在會話期間存活，但是這類資料只能被包中程式使用，即為私人資料。
另一方面，如果是在包聲明中定義包資料則對所有具有執行包許可權的程式都可使用。

來看一個例子：
DBMS_UTILITY包中GET_CPU_TIME函數可用來計算你的程式耗時

Code Listing 5: DBMS_UTILITY.GET_CPU_TIME measures

DECLARE   l_start   PLS_INTEGER;BEGIN   /* Get and save the starting time. */   l_start := DBMS_UTILITY.get_cpu_time;   /* Run your code. */   FOR indx IN 1 .. 10000   LOOP      NULL;   END LOOP;   /* Subtract starting time from current time. */   DBMS_OUTPUT.put_line (      DBMS_UTILITY.get_cpu_time - l_start);END;/

看著足夠簡單了吧，但是你還是需要聲明一個本地變數來存放耗時！
so，我們有更快捷的方式，使用自訂包timer_pkg!!!

Code Listing 6: The timer_pkg package

CREATE OR REPLACE PACKAGE timer_pkgIS   PROCEDURE start_timer;   PROCEDURE show_elapsed (message_in IN VARCHAR2 := NULL);END timer_pkg;/CREATE OR REPLACE PACKAGE BODY timer_pkgIS   g_start_time   NUMBER := NULL;   PROCEDURE start_timer   IS   BEGIN      g_start_time := DBMS_UTILITY.get_cpu_time;   END;   PROCEDURE show_elapsed (message_in IN VARCHAR2 := NULL)   IS   BEGIN      DBMS_OUTPUT.put_line (            message_in         || ': '         || TO_CHAR (DBMS_UTILITY.get_cpu_time - g_start_time));      start_timer;   END;END timer_pkg;/ 

改寫之前的匿名塊，如下：

BEGIN   timer_pkg.start_timer;   FOR indx IN 1 .. 10000   LOOP      NULL;   END LOOP;   timer_pkg.show_elapsed ('10000 Nothings');END;/

哇哦！good job！

不再需要聲明本地變數，不再需要理解GET_CPU_TIME function 如何工作！

3 子程式重載
我們都知道DBMS_OUTPUT.PUT_LINE用於往控制台列印字元資料，

BEGIN   DBMS_OUTPUT.PUT_LINE (100);END;

其有一個弊端，只能輸出字元類型！

SQL> BEGIN  2     DBMS_OUTPUT.PUT_LINE (TRUE);  3  END;  4  /   DBMS_OUTPUT.PUT_LINE (TRUE);   *ERROR at line 2:ORA-06550: line 2, column 4:PLS-00306: wrong number or types of arguments in call to ‘PUT_LINE’

多尷尬！ 比較BOOLEAN類型無法轉成字元類型！

很多開發人員不得不這麼搞：

IF l_student_is_registeredTHEN   DBMS_OUTPUT.PUT_LINE ('TRUE');ELSE   DBMS_OUTPUT.PUT_LINE ('FALSE');END IF;

不得不說精神可嘉！
但是，我們有更好的方式：
Code Listing 7: The my_output package without overloading

CREATE OR REPLACE PACKAGE my_outputIS   PROCEDURE put_line (value_in IN VARCHAR2);   PROCEDURE put_line (value_in IN BOOLEAN);   PROCEDURE put_line (      value_in   IN DATE,      mask_in    IN VARCHAR2 DEFAULT 'YYYY-MM-DD HH24:MI:SS');END my_output;/

這就充分發揮了重載的價值！

4 包狀態及ORA-04068錯誤
這個問題是任何開發包的人都無法迴避的。
包有狀態？
當一個包有至少一個常量或變數聲明在包層級，該包就有了狀態！
當一個會話調用有狀態包，PGA將包所有包層級資料存放區起來！

如果一個狀態包重新編譯，所有使用該包的會話在下次調用時都會拋出：ORA-04068錯誤。
因為儲存在PGA中包層級資料已經到期了（out of date）！所以包必須再次初始化！

此外，一旦ORA-04068拋出，會話中所有狀態包，例如，DBMS_OUTPUT都將標識為未初始化。這通常意味著使用者
必須斷開會話重新串連。

這個潛在的錯誤意味著當IT部門需要升級應用，他們需要確保所有使用者已登出。 但是在7*24的互連網世界這是
不能容忍的。

所以在Oracle 11g r2中，oracle提供了基於版本的重定義功能（Edition-Based Redefinition feature）。

詳細請參考：oracle.com/technetwork/database/features/availability/edition-based-redefinition-1-133045.pdf and docs.oracle.com/cd/E11882_01/appdev.112/e10471/adfns_editions.htm

Oracle資料庫之PL/SQL程式基礎設計 

PL/SQL Developer實用技巧分享