Oracle Package 完整指南：封裝、權限控制、重載與實戰最佳實踐

為什麼需要 Package？

隨著存儲過程、函數增長，預設全置於 USER_PROCEDURES 扁平列表中，管理極其困難。Package 提供 **命名空間隔離** 與 **邏輯模組化**，將相關程序、函數、游標、型別、變數、常數、例外集中封裝，極大降低維護成本。

Package 兩大組成部分

規格 = 介面契約，主體 = 實作細節。修改主體不影響呼叫端（只要規格不變），實現**介面與實作分離**。

建立 Package 實戰範例

規格

CREATE OR REPLACE PACKAGE pack_test AS
  -- 公共變數/常數
  g_batch_no   VARCHAR2(10);
  c_max_retry  CONSTANT NUMBER := 3;
  -- 公共型別
  TYPE emp_rec IS RECORD (emp_id VARCHAR2(10), sal NUMBER(20,2));
  -- 公共游標
  CURSOR emp_cur RETURN emp_rec;
  -- 公共例外
  e_invalid_sal EXCEPTION;
  -- 公共程序/函數
  FUNCTION hire_emp (p_name VARCHAR2, p_age NUMBER, p_sex VARCHAR2) RETURN NUMBER;
  PROCEDURE fire_emp (p_emp_id VARCHAR2);
END pack_test;

主體

CREATE OR REPLACE PACKAGE BODY pack_test AS
  -- 私有常數/變數（外部不可見）
  c_default_sal CONSTANT NUMBER := 5000;
  g_total_cnt   NUMBER := 0;

  -- 游標實作
  CURSOR emp_cur RETURN emp_rec IS
    SELECT emp_id, salary FROM employee;

  -- 函數實作
  FUNCTION hire_emp (p_name VARCHAR2, p_age NUMBER, p_sex VARCHAR2) RETURN NUMBER IS
  BEGIN
    DBMS_OUTPUT.PUT_LINE('Hire: '||p_name||' Age:'||p_age||' Sex:'||p_sex);
    g_total_cnt := g_total_cnt + 1;
    RETURN 1;
  END;

  -- 程序實作
  PROCEDURE fire_emp (p_emp_id VARCHAR2) IS
  BEGIN
    DBMS_OUTPUT.PUT_LINE('Fire: '||p_emp_id);
    g_total_cnt := g_total_cnt - 1;
  END;

  -- 初始化區塊（首次引用包時自動執行一次）
  BEGIN
    g_total_cnt := 0;
  END pack_test;

編譯 Package

查看錯誤：SHOW ERRORS PACKAGE pack_test; / SHOW ERRORS PACKAGE BODY pack_test;

呼叫 Package 內元素

• SQL*Plus：EXECUTE pack_test.fire_emp('3008732');
• PL/SQL 區塊：i := pack_test.hire_emp('Jelly',23,'M'); pack_test.fire_emp('660045214');

呼叫格式：package_name.element_name，與 Java/C# 命名空間呼叫一致。

刪除 Package

DROP PACKAGE BODY pack_test;  -- 僅刪主體，規格保留
DROP PACKAGE pack_test;       -- 刪除整包（規格+主體）

需具備 DROP ANY PROCEDURE 權限。

Package 重載

同一 Package 內允許**同名程序/函數**，參數**個數或型別不同**。規則：

• ✅ 參數個數不同
• ✅ 參數資料型別不同（非同族型別，如 NUMBER vs VARCHAR2）
• ❌ 僅參數模式不同（IN vs OUT）
• ❌ 僅返回型別不同（函數）
• ❌ 同族型別（CHAR vs VARCHAR2、NUMBER vs INTEGER）

PROCEDURE proc(p VARCHAR2);
PROCEDURE proc(p NUMBER);       -- 合法重載
PROCEDURE proc(p IN VARCHAR2);
PROCEDURE proc(p OUT VARCHAR2); -- 非法：僅模式不同

最佳實踐建議

1. 按業務域劃分 Package： 如 PKG_AR (應收)、PKG_GL (總帳)、PKG_COMMON (共用工具)。
2. 規格極簡、主體豐富： 規格僅暴露必要介面，業務邏輯、輔助函數、快取變數全置於主體私有區。
3. 善用初始化區塊： 單例模式、快取預熱、配置讀取放在 BEGIN ... END package_name;。
4. 版本管理： 規格變更需同步所有呼叫端，建立發布流程（編譯規格 → 執行測試 → 部署主體）。

總結

Package 是 PL/SQL 的「類」機制：**規格定介面、主體藏實作、權限分公私、支援重載**。善用 Package 將散亂程式單元模組化，是大型 PL/SQL 專案可維護性的關鍵。