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

Oracle 集合完整指南：方法、儲存與索引表實戰

編輯：Oracle 資料庫相關知識

Oracle 集合概述

集合是 Oracle PL/SQL 中處理多值資料結構的核心功能，對應其他語言的陣列。Oracle 提供三種集合類型：關聯陣列、巢狀表、VARRAY。前者僅存在於 PL/SQL 記憶體中，後兩者可持久化儲存於資料庫。

集合通用方法速查表

方法	類型	說明	語法範例
EXISTS	函數	檢查索引是否存在，回傳 TRUE/FALSE	`coll.EXISTS(idx)`
COUNT	函數	回傳集合元素總數	`coll.COUNT`
DELETE	程序	刪除元素：無參數刪全部、單一索引、區間	`coll.DELETE / coll.DELETE(i) / coll.DELETE(i,j)`
FIRST	函數	回傳第一個索引值，空集合回傳 NULL	`coll.FIRST`
LAST	函數	回傳最後一個索引值，空集合回傳 NULL	`coll.LAST`
NEXT	函數	回傳指定索引的下一個索引	`coll.NEXT(idx)`
PRIOR	函數	回傳指定索引的上一個索引	`coll.PRIOR(idx)`
LIMIT	函數	僅 VARRAY 適用，回傳最大元素數	`coll.LIMIT`
EXTEND	程序	在尾端擴展元素：單一 NULL、N 個 NULL、N 個指定索引副本	`coll.EXTEND / coll.EXTEND(n) / coll.EXTEND(n, idx)`
TRIM	程序	從尾端移除元素：單一、N 個	`coll.TRIM / coll.TRIM(n)`

集合持久化儲存於資料庫

巢狀表與 VARRAY 可作為資料表欄位或物件屬性儲存。必須使用 CREATE OR REPLACE TYPE 在 SQL 層級定義，不可僅在 PL/SQL 區塊區域定義。

-- 建立巢狀表類型
CREATE OR REPLACE TYPE emp_tab_type IS TABLE OF emp%ROWTYPE;

-- 用於資料表欄位
CREATE TABLE dept_emps (
  deptno NUMBER,
  emps   emp_tab_type
) NESTED TABLE emps STORE AS emps_nt;

索引組織表實戰範例

索引表僅存在於記憶體，索引鍵可為負數、非連續，適合稀疏資料處理。

DECLARE
  TYPE t_indexTable IS TABLE OF emp%ROWTYPE 
    NOT NULL INDEX BY BINARY_INTEGER;
  v_indexTable t_indexTable;
  CURSOR v_cur IS SELECT * FROM emp;
BEGIN
  FOR c IN v_cur LOOP
    v_indexTable(c.empno) := c;  -- 以 empno 為索引鍵
    IF v_indexTable.EXISTS(c.empno) THEN
      DBMS_OUTPUT.PUT_LINE('empno=' || c.empno || ', ename=' || c.ename);
    ELSE
      DBMS_OUTPUT.PUT_LINE('不存在');
    END IF;
  END LOOP;
END;

關鍵差異：

INDEX BY BINARY_INTEGER → 索引表（關聯陣列）
無 INDEX BY 子句 → 巢狀表
索引表下標可不連續，可刪除特定下標；VARRAY 下標必須連續

三種集合類型選擇指南

特性	關聯陣列	巢狀表	VARRAY
儲存位置	僅 PL/SQL 記憶體	資料庫（可持久化）	資料庫（可持久化）
索引類型	BINARY_INTEGER / VARCHAR2	正整數 (1..N)	正整數 (1..N)
大小限制	無上限	無上限	宣告時固定上限
稀疏性	支援	支援（刪除中間元素後）	不支援（永遠緻密）
適用場景	暫存運算、查找表	資料表欄位、不定大小集合	固定大小、有序集合

常見操作模式

遍歷集合

-- 密集集合
FOR i IN coll.FIRST .. coll.LAST LOOP
  IF coll.EXISTS(i) THEN
    -- 處理 coll(i)
  END IF;
END LOOP;

-- 稀疏集合（推薦）
idx := coll.FIRST;
WHILE idx IS NOT NULL LOOP
  -- 處理 coll(idx)
  idx := coll.NEXT(idx);
END LOOP;

集合與 SQL 互動

-- 批次綁定（FORALL）提升效能
FORALL i IN INDICES OF coll
  INSERT INTO target_table VALUES coll(i);

-- 批次查詢
SELECT col BULK COLLECT INTO coll FROM source_table;