Oracle Package 建立語法完整指南：規格與主體分離設計

Package 架構優勢

• 封裝性：將相關程序、函數、游標、型別、變數、常數、例外集中管理。
• 狀態保持：Package 級變數在 Session 生命週期內持久化，跨呼叫共享狀態。
• 權限整體授予：GRANT EXECUTE ON pkg TO user 一次授予所有公開元素。
• 多載：同名程序/函數可依參數簽章區分。
• 編譯依賴最小化：修改 Body 不影響 Spec，減少無效化連鎖。

Package Specification（規格/介面）

CREATE [OR REPLACE] PACKAGE [schema.]pkg_name
{AS | IS}
  -- 公開元素（任意順序）
  public_variable_declarations   -- 常數、變數、集合、記錄型別變數
  public_type_declarations       -- RECORD、TABLE、VARRAY、REF CURSOR 型別
  public_exception_declarations  -- 自訂例外
  public_cursor_declarations     -- 顯式游標（含 RETURN 型別）
  function_declarations          -- 函數標頭（含 RETURN 型別）
  procedure_specifications       -- 程序標頭
END [pkg_name];

Package Body（主體/實作）

CREATE [OR REPLACE] PACKAGE BODY [schema.]pkg_name
{AS | IS}
  -- 私有元素（外部不可見）
  private_variable_declarations
  private_type_declarations
  private_exception_declarations
  private_cursor_declarations
  function_declarations          -- 完整實作
  procedure_declarations         -- 完整實作
  -- 可選：初始化區塊（Package 首次被引用時自動執行一次）
  BEGIN
    -- initialization statements
  END [pkg_name];

完整範例：員工薪資管理 Package

Spec

CREATE OR REPLACE PACKAGE emp_salary_pkg AS
  -- 公開常數
  MIN_SALARY CONSTANT NUMBER := 20000;
  MAX_SALARY CONSTANT NUMBER := 200000;

  -- 公開型別
  TYPE emp_rec IS RECORD (
    empno    emp.empno%TYPE,
    ename    emp.ename%TYPE,
    sal      emp.sal%TYPE
  );
  TYPE emp_tab IS TABLE OF emp_rec INDEX BY PLS_INTEGER;

  -- 公開例外
  e_salary_out_of_range EXCEPTION;
  PRAGMA EXCEPTION_INIT(e_salary_out_of_range, -20001);

  -- 公開函數/程序標頭
  FUNCTION get_avg_sal(p_deptno IN emp.deptno%TYPE) RETURN NUMBER;
  PROCEDURE adjust_salary(p_empno IN emp.empno%TYPE, p_pct IN NUMBER);
  PROCEDURE adjust_salary(p_ename IN emp.ename%TYPE, p_pct IN NUMBER); -- 多載
END emp_salary_pkg;
/

Body

CREATE OR REPLACE PACKAGE BODY emp_salary_pkg AS
  -- 私有變數：記錄最後調整時間
  g_last_adjusted DATE;

  -- 私有函數
  FUNCTION validate_sal(p_sal IN NUMBER) RETURN BOOLEAN IS
  BEGIN
    RETURN p_sal BETWEEN MIN_SALARY AND MAX_SALARY;
  END;

  -- 公開函數實作
  FUNCTION get_avg_sal(p_deptno IN emp.deptno%TYPE) RETURN NUMBER IS
    v_avg NUMBER;
  BEGIN
    SELECT AVG(sal) INTO v_avg FROM emp WHERE deptno = p_deptno;
    RETURN NVL(v_avg, 0);
  END;

  -- 公開程序實作（多載 1）
  PROCEDURE adjust_salary(p_empno IN emp.empno%TYPE, p_pct IN NUMBER) IS
    v_new_sal emp.sal%TYPE;
  BEGIN
    SELECT sal * (1 + p_pct/100) INTO v_new_sal FROM emp WHERE empno = p_empno;
    IF NOT validate_sal(v_new_sal) THEN
      RAISE e_salary_out_of_range;
    END IF;
    UPDATE emp SET sal = v_new_sal WHERE empno = p_empno;
    g_last_adjusted := SYSDATE;
  END;

  -- 公開程序實作（多載 2）
  PROCEDURE adjust_salary(p_ename IN emp.ename%TYPE, p_pct IN NUMBER) IS
    v_empno emp.empno%TYPE;
  BEGIN
    SELECT empno INTO v_empno FROM emp WHERE ename = p_ename;
    adjust_salary(v_empno, p_pct); -- 重用多載 1
  END;

  -- 初始化區塊
  BEGIN
    g_last_adjusted := NULL;
  END emp_salary_pkg;
/

呼叫方式

-- SQL 中呼叫函數
SELECT emp_salary_pkg.get_avg_sal(10) FROM dual;

-- PL/SQL 中呼叫程序
BEGIN
  emp_salary_pkg.adjust_salary(7369, 10);
END;
/

最佳實踐提醒

• Spec 只放「介面」，Body 才放「實作細節」，降低耦合。
• 善用私有元素隱藏實作細節，對外僅暴露必要 API。
• 初始化區塊僅執行一次，適合載入設定、快取靜態資料。
• 大型 Package 拆分為多個功能內聚的小 Package，避免「上帝物件」。