Oracle 存儲過程需返回多行多欄資料時,可使用 集合型別 搭配 OUT 參數。主流方案為在套件中定義 TABLE OF 或 VARRAY 型別,再於存儲過程中實例化、填充並返回。
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;
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;
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;
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 並指定完整型別名稱 (含套件名) |
TABLE(pkg.func(...)),整合性最佳;XMLTYPE 或 JSON_ARRAY,適合異構系統整合。套件定義集合型別是 Oracle 存儲過程返回結構化陣列的標準做法。掌握初始化、擴展、異常處理與跨語言映射細節,能構建健壯的資料交換介面。