您现在的位置：程式碼之家 >> 數據庫知識 > >> Oracle 資料庫 >> Oracle 資料庫基礎

Oracle PL/SQL 集合類型作為 OUT 參數：索引表實戰詳解

編輯：Oracle 資料庫基礎

前言：為何需要集合輸出參數

標量參數 (NUMBER、VARCHAR2) 只能返回單值。實際統計查詢常需返回「結果集」或「二維數組」，PL/SQL 集合正是為此設計：一次傳遞多元素，支持整體操作與單元素訪問，靈活高效。

一、三大集合類型對比

類型	結構	大小限制	初始化	適用場景
索引表 (Index-by Table)	稀疏、無序、Key 為 BINARY_INTEGER	受限於 Key 範圍 (-2G~+2G)	無需初始化，直接賦值	臨時計算、PL/SQL 內部傳遞、OUT 參數首選
嵌套表	稠密、有序、可存儲於數據庫列	無上限 (受存儲限制)	需構造函數初始化	持久化存儲、列屬性
VARRAY	固定長度、有序	定義時指定最大元素數	需構造函數初始化	已知最大量、順序重要的小集合

二、索引表作為 OUT 參數的完整實現

1. 包規範中定義類型 (強制要求)

CREATE OR REPLACE PACKAGE out_param IS
  -- 定義元素為 VARCHAR2(50) 的索引表類型
  TYPE out_index_table_typ IS TABLE OF VARCHAR2(50) INDEX BY BINARY_INTEGER;
END out_param;
/

關鍵點：OUT 參數的集合類型必須在包中定義，否則編譯報錯 "PLS-00382: expression is of wrong type"。

2. 存儲過程引用包類型

CREATE OR REPLACE PROCEDURE testPro1 (
  in_param  IN  VARCHAR2,
  o_table   OUT out_param.out_index_table_typ  -- 包名.類型名
) IS
BEGIN
  -- 示例：模擬查詢返回多行結果
  o_table(1) := 'Row 1 Data';
  o_table(2) := 'Row 2 Data';
  o_table(100) := 'Sparse Key 100';  -- 稀疏鍵演示
  -- 實際業務中可循環賦值：o_table(i) := cursor_rec.col_val;
END testPro1;
/

3. Java/JDBC 調用示例 (Oracle JDBC 支持)

CallableStatement cs = conn.prepareCall("{call testPro1(?, ?)}");
cs.setString(1, "input_val");
cs.registerOutParameter(2, OracleTypes.ARRAY, "OUT_PARAM.OUT_INDEX_TABLE_TYP");
// 注意：索引表無法直接註冊為 ARRAY，實際常用 REF CURSOR 或嵌套表替代
// 此處演示邏輯，實際生產建議用 SYS_REFCURSOR 返回結果集
cs.execute();
/*
// 若用嵌套表類型 (需 CREATE TYPE 而非包內定義)
Array arr = cs.getArray(2);
Object[] data = (Object[]) arr.getArray();
*/
cs.close();

三、實戰避坑指南

包級定義強制性：OUT/IN OUT 集合參數類型必須在包規格中聲明，匿名塊或過程內部定義無效。
稀疏特性利用：索引表 Key 可不連續，適合「按業務 ID 存取」場景，如 o_table(dept_id) := dept_name。
跨語言調用限制：JDBC 不直接支持索引表 OUT 參數。生產建議：
1. 改用 SYS_REFCURSOR 返回結果集 (通用、標準、驅動支持最好)；
2. 或定義 Schema 級 CREATE TYPE nt_type AS TABLE OF VARCHAR2(50) (嵌套表) 映射 OracleTypes.ARRAY。
內存控制：大量數據慎用集合 OUT 參數，避免 PGA 暴增；分頁或游標流式處理更安全。