Oracle 存儲過程與 Java 調用完整實戰：物件陣列傳遞與 REF CURSOR 返回

需求背景

Java 端需傳遞多組 (userid, topicid) 參數給 Oracle 存儲過程，存儲過程循環查詢點贊關係表 tb_praise_rel，判斷每組是否存在記錄（存在 record=1，否則 0），最終返回一個包含 userid, topicid, record 的結果集。

資料庫端實作

1. 定義物件型別與集合型別

-- 單條記錄型別
CREATE OR REPLACE TYPE tp_arr3 AS OBJECT (
    userid  NVARCHAR2(40),
    topicid NVARCHAR2(40),
    record  NVARCHAR2(4)
);

-- 集合型別（對應 Java Array）
CREATE OR REPLACE TYPE tp_arr_tbl3 AS TABLE OF tp_arr3;

關鍵點：使用 NVARCHAR2 對應 Java String，避免字符集不匹配導致 Message file 'oracle.jdbc.driver.Messages' is missing 錯誤。

2. 定義包與 REF CURSOR

CREATE OR REPLACE PACKAGE testpkg AS
    TYPE testcur IS REF CURSOR;
END testpkg;

3. 存儲過程實作

CREATE OR REPLACE PROCEDURE findRecord(
    type_obj IN tp_arr_tbl3,
    result   OUT testpkg.testcur
) AS
    t        tp_arr3;
    v_count  VARCHAR2(4);
    sql_drop VARCHAR2(200) := 'DROP TABLE tb_temp';
    sql_create VARCHAR2(500) := 'CREATE TABLE tb_temp(userid VARCHAR2(40), topicid VARCHAR2(40) PRIMARY KEY, record VARCHAR2(4))';
BEGIN
    -- 每次執行重建臨時表，避免主鍵衝突
    EXECUTE IMMEDIATE sql_drop;
    EXECUTE IMMEDIATE sql_create;

    FOR i IN type_obj.FIRST .. type_obj.LAST LOOP
        t := type_obj(i);
        SELECT COUNT(*) INTO v_count
        FROM scott.tb_praise_rel
        WHERE userid = t.userid AND topicid = t.topicid;

        INSERT INTO tb_temp VALUES (t.userid, t.topicid, v_count);
    END LOOP;

    COMMIT;
    OPEN result FOR SELECT * FROM tb_temp;
END findRecord;

Java 端調用實作

1. 依賴與 Bean 定義

<dependency>
    <groupId>com.oracle.database.jdbc</groupId>
    <artifactId>ojdbc8</artifactId>
    <version>21.9.0.0</version>
</dependency>

public class ProcedureBean implements Serializable {
    private String userid, topicid, record;
    // getter/setter/toString 省略
}

2. 核心調用代碼

public class ProcedureTest3 {
    public static void main(String[] args) throws Exception {
        Class.forName("oracle.jdbc.driver.OracleDriver");
        Connection con = DriverManager.getConnection(
            "jdbc:oracle:thin:@localhost:1521:orcl", "system", "a");

        // 1. 準備輸入參數集合
        List<ProcedureBean> list = new ArrayList<>();
        for (int i = 1; i <= 12; i++) {
            list.add(new ProcedureBean("1", String.valueOf(i), "0"));
        }

        // 2. 映射 tp_arr3 (STRUCT)
        StructDescriptor recDesc = StructDescriptor.createDescriptor("TP_ARR3", con);
        List<STRUCT> pstruct = new ArrayList<>();
        for (ProcedureBean pb : list) {
            Object[] objs = {pb.getUserid(), pb.getTopicid(), pb.getRecord()};
            pstruct.add(new STRUCT(recDesc, con, objs));
        }

        // 3. 映射 tp_arr_tbl3 (ARRAY)
        ArrayDescriptor desc = ArrayDescriptor.createDescriptor("TP_ARR_TBL3", con);
        ARRAY array = new ARRAY(desc, con, pstruct.toArray());

        // 4. 呼叫存儲過程
        CallableStatement cs = con.prepareCall("{call findRecord(?,?)}");
        cs.setArray(1, array);
        cs.registerOutParameter(2, OracleTypes.CURSOR);
        cs.execute();

        // 5. 處理結果集
        ResultSet rs = (ResultSet) cs.getObject(2);
        while (rs.next()) {
            System.out.println("result: " + rs.getString(1) + "\t" + rs.getString(2) + "\t" + rs.getString(3));
        }
        con.commit();
        cs.close();
        con.close();
    }
}

常見坑點總結

• 型別名稱大小寫：StructDescriptor.createDescriptor 參數必須大寫（如 TP_ARR3），否則報「無效名稱模式」。
• ta.extend() 不可漏：PL/SQL 測試時集合必須先 EXTEND 再賦值。
• 自定義型別若報 ORA-02303，換個型別名重建即可。
• JAR 包衝突：僅保留一個 ojdbc*.jar，移除 class12.jar 等舊包。
• 連接字串不需 getInnermostDelegate()，直連即可。

測試表結構

CREATE TABLE tb_praise_rel (
    id       VARCHAR2(40) PRIMARY KEY,
    userid   VARCHAR2(40),
    topicid  VARCHAR2(40),
    remarks1 VARCHAR2(3000),
    remarks2 VARCHAR2(3000),
    remarks3 VARCHAR2(3000)
);