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

將程式碼封裝在靈巧的包中
http://www.oracle.com/technetwork/issue-archive/2013/13-jan/o13plsql-1872456.html

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

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

通常由包宣告（物件宣告）和包體（具體實現）組成。

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

下面展示一下包的魅力：

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

SQL> desc employees

Name             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_pkg
2    AS
3        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_pkg
2    AS
3       FUNCTION fullname (
4          last_in employees.last_name%TYPE,
5          first_in employees.first_name%TYPE
6       )
7          RETURN fullname_t
8       IS
9       BEGIN
10         RETURN last_in || ', ' || first_in;
11      END;
12     
13      FUNCTION fullname (employee_id_in IN employee.employee_id%TYPE)
14         RETURN fullname_t
15      IS
16         l_fullname fullname_t;
17      BEGIN
18         SELECT fullname (last_name, first_name) INTO l_fullname
19           FROM employees
20          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_limits
IS
   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_pkg
IS
   PROCEDURE start_timer;

   PROCEDURE show_elapsed (message_in IN VARCHAR2 := NULL);
END timer_pkg;
/

CREATE OR REPLACE PACKAGE BODY timer_pkg
IS
   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_registered
THEN
   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_output
IS
   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