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

Oracle PL/SQL 動態建構與填充 Collection：EXTEND、初始化與元素賦值完整範例

編輯：Oracle 資料庫基礎

應用場景

需在程序內部「從零開始」建立一個 Collection，逐筆填入資料（可能來源於查詢、計算或外部輸入），最後回傳給呼叫端或批次寫入資料表。

類型定義（沿用前文）

CREATE OR REPLACE TYPE department_type AS OBJECT (
  dno      NUMBER(10),
  name     VARCHAR2(50),
  location VARCHAR2(50)
);

CREATE OR REPLACE TYPE dept_array AS TABLE OF department_type;

核心程序：建構並填充 Collection

CREATE OR REPLACE PACKAGE BODY obj_pkg AS
  PROCEDURE build_dept_array(
    p_src_depts  IN  dept_array,   -- 來源資料（可為空）
    p_new_depts  OUT NOCOPY dept_array  -- 新建並回傳
  ) IS
    v_idx   PLS_INTEGER := 0;
  BEGIN
    -- 1. 初始化：必須先呼叫建構函數配置記憶體
    p_new_depts := dept_array();

    -- 2. 從來源複製並轉換（示範：依來源建立新物件）
    IF p_src_depts IS NOT NULL THEN
      FOR i IN 1 .. p_src_depts.COUNT LOOP
        p_new_depts.EXTEND;  -- 擴充一個元素位置
        v_idx := p_new_depts.LAST;  -- 取得新索引
        p_new_depts(v_idx) := department_type(
          p_src_depts(i).dno,
          p_src_depts(i).name,
          p_src_depts(i).location || '_COPY'  -- 轉換邏輯
        );
      END LOOP;
    END IF;

    -- 3. 手動新增測試資料（示範：非來源資料）
    FOR j IN 1 .. 3 LOOP
      p_new_depts.EXTEND;
      p_new_depts(p_new_depts.LAST) := department_type(
        100 + j, 'DEPT_' || j, 'Location_' || j
      );
    END LOOP;

    -- 4. 修改剛建立的元素（合法，因為是 IN OUT NOCOPY）
    FOR k IN 1 .. p_new_depts.COUNT LOOP
      p_new_depts(k).location := p_new_depts(k).location || '_UPDATED';
    END LOOP;

    -- 5. 批次寫入資料表（FORALL 效能最佳）
    FORALL idx IN INDICES OF p_new_depts
      INSERT INTO department_test VALUES (
        p_new_depts(idx).dno,
        p_new_depts(idx).name,
        p_new_depts(idx).location
      );

  EXCEPTION
    WHEN OTHERS THEN
      -- 記錄錯誤並拋出
      DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
      RAISE;
  END build_dept_array;
END obj_pkg;

關鍵方法對照表

方法	用途	範例
`dept_array()`	建構函數，初始化空集合	`v_arr := dept_array();`
`EXTEND`	在末尾新增 1 個 NULL 元素	`v_arr.EXTEND;`
`EXTEND(n)`	新增 n 個 NULL 元素	`v_arr.EXTEND(5);`
`EXTEND(n, v)`	新增 n 個元素，內容複製自索引 v	`v_arr.EXTEND(3, 1);`
`TRIM`	移除末尾 1 個元素	`v_arr.TRIM;`
`TRIM(n)`	移除末尾 n 個元素	`v_arr.TRIM(2);`
`DELETE`	移除所有元素	`v_arr.DELETE;`
`DELETE(i)`	移除索引 i	`v_arr.DELETE(3);`
`DELETE(i,j)`	移除索引 i~j	`v_arr.DELETE(2,5);`
`EXISTS(i)`	檢查索引 i 是否存在	`IF v_arr.EXISTS(1) THEN ...`
`FIRST/LAST`	第一個/最後一個索引	`FOR i IN v_arr.FIRST..v_arr.LAST`
`NEXT(i)/PRIOR(i)`	下一個/上一個存在的索引	`i := v_arr.NEXT(i);`
`COUNT`	元素總數	`v_arr.COUNT`
`LIMIT`	最大容量（VARRAY 才有）	`v_arr.LIMIT`

呼叫範例

DECLARE
  v_src   dept_array := dept_array(department_type(1,'A','LocA'));
  v_new   dept_array;
BEGIN
  obj_pkg.build_dept_array(v_src, v_new);
  DBMS_OUTPUT.PUT_LINE('New count: ' || v_new.COUNT);  -- 4
  FOR i IN 1..v_new.COUNT LOOP
    DBMS_OUTPUT.PUT_LINE(v_new(i).name || ' - ' || v_new(i).location);
  END LOOP;
END;