萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> Oracle 教學

Oracle 存儲過程返回陣列:套件定義集合型別與實作範例

核心概念

Oracle 存儲過程需返回多行多欄資料時,可使用 集合型別 搭配 OUT 參數。主流方案為在套件中定義 TABLE OF 或 VARRAY 型別,再於存儲過程中實例化、填充並返回。

完整實作範例

1. 定義套件規格

CREATE OR REPLACE PACKAGE pkg_array_demo AS
  -- 定義標量陣列型別 (VARCHAR2 元素)
  TYPE t_varchar2_tab IS TABLE OF VARCHAR2(100);
  
  -- 定義記錄型別 (模擬多欄位)
  TYPE t_emp_rec IS RECORD (
    empno    NUMBER(4),
    ename    VARCHAR2(20),
    job      VARCHAR2(15),
    hiredate DATE
  );
  TYPE t_emp_tab IS TABLE OF t_emp_rec;
  
  -- 存儲過程介面
  PROCEDURE get_dept_employees (
    p_deptno  IN  NUMBER,
    p_emp_arr OUT t_emp_tab
  );
END pkg_array_demo;

2. 套件主體實作

CREATE OR REPLACE PACKAGE BODY pkg_array_demo AS
  PROCEDURE get_dept_employees (
    p_deptno  IN  NUMBER,
    p_emp_arr OUT t_emp_tab
  ) AS
    CURSOR c_emp IS
      SELECT empno, ename, job, hiredate
      FROM   emp
      WHERE  deptno = p_deptno
      ORDER  BY empno;
  BEGIN
    -- 初始化集合
    p_emp_arr := t_emp_tab();
    
    FOR r_emp IN c_emp LOOP
      p_emp_arr.EXTEND;
      p_emp_arr(p_emp_arr.LAST) := r_emp;
    END LOOP;
    
    -- 若無資料返回空集合而非 NULL
    IF p_emp_arr.COUNT = 0 THEN
      p_emp_arr := t_emp_tab(); -- 空集合
    END IF;
  EXCEPTION
    WHEN OTHERS THEN
      -- 記錄錯誤日誌
      RAISE;
  END;
END pkg_array_demo;

呼叫端範例

PL/SQL 匿名區塊測試

DECLARE
  v_emps pkg_array_demo.t_emp_tab;
BEGIN
  pkg_array_demo.get_dept_employees(10, v_emps);
  
  FOR i IN 1..v_emps.COUNT LOOP
    DBMS_OUTPUT.PUT_LINE(
      v_emps(i).empno || ' | ' || 
      v_emps(i).ename || ' | ' || 
      v_emps(i).job
    );
  END LOOP;
END;

Java/JDBC 呼叫 (Oracle 專用)

CallableStatement cs = conn.prepareCall(
  "{call pkg_array_demo.get_dept_employees(?, ?)}"
);
cs.setInt(1, 10);
cs.registerOutParameter(2, OracleTypes.ARRAY, "PKG_ARRAY_DEMO.T_EMP_TAB");
cs.execute();

ARRAY oracleArray = (ARRAY) cs.getObject(2);
Object[] javaArray = (Object[]) oracleArray.getArray();
for (Object obj : javaArray) {
  STRUCT struct = (STRUCT) obj;
  Object[] attrs = struct.getAttributes();
  System.out.println(attrs[0] + " | " + attrs[1] + " | " + attrs[2]);
}
cs.close();

關鍵技術要點

項目說明
集合初始化必須呼叫建構子 t_emp_tab(),否則集合為 NULL,呼叫 EXTEND 會拋出 ORA-06531
EXTEND 用法每次新增元素前需呼叫 EXTEND,或使用 EXTEND(n) 一次擴展 n 個
索引特性PL/SQL 集合索引從 1 起始,連續且無間隙
記憶體管理大量資料時集合駐留 PGA,注意 PGA_AGGREGATE_LIMIT 設定
跨語言傳遞JDBC 需註冊 OracleTypes.ARRAY 並指定完整型別名稱 (含套件名)

替代方案比較

  • REF CURSOR:串流式返回,記憶體佔用低,適合大量資料,但需呼叫端逐行提取;
  • PIPELINED FUNCTION:可直接在 SQL 中使用 TABLE(pkg.func(...)),整合性最佳;
  • XML/JSON 文檔:12c+ 可返回 XMLTYPE 或 JSON_ARRAY,適合異構系統整合。

總結

套件定義集合型別是 Oracle 存儲過程返回結構化陣列的標準做法。掌握初始化、擴展、異常處理與跨語言映射細節,能構建健壯的資料交換介面。

copyright © 萬盛學電腦網 all rights reserved