JSP 調用 Oracle 存儲過程完整實戰：從 IN/OUT 參數到 REF CURSOR 返回結果集

Oracle 存儲過程在企業級應用中的地位

Oracle 資料庫的 PL/SQL 存儲過程憑藉強大的過程化能力、就近處理資料減少網路傳輸、執行計劃緩存復用、安全權限控制等優勢，長期佔據企業級核心業務邏輯的主導地位。JSP/Servlet 應用作為展示層，高效、安全地調用存儲過程是必備技能。本文系統講解參數綁定、結果集處理、異常傳播、效能優化全流程。

Oracle 存儲過程參數模式詳解

基礎範例：帶 IN 參數的插入過程

Oracle 端定義

CREATE OR REPLACE PROCEDURE p_insert_log (
    p_module   IN VARCHAR2,
    p_level    IN VARCHAR2,
    p_message  IN VARCHAR2,
    p_log_id   OUT NUMBER
) AS
BEGIN
    INSERT INTO sys_log (module, level, message, create_time)
    VALUES (p_module, p_level, p_message, SYSTIMESTAMP)
    RETURNING log_id INTO p_log_id; -- 直接返回自增值，無需額外查詢
    COMMIT;
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        RAISE; -- 向上拋出異常，JDBC 可捕獲 SQLException
END;
/

JSP 調用代碼

<%@ page import="java.sql.*, oracle.jdbc.*" %>
<%
    String url = "jdbc:oracle:thin:@//192.168.1.100:1521/ORCL";
    String user = "app_user", pwd = "secure_pwd";
    
    try (Connection conn = DriverManager.getConnection(url, user, pwd);
         CallableStatement cstmt = conn.prepareCall("{call p_insert_log(?, ?, ?, ?)}")) {
        
        cstmt.setString(1, "OrderService");
        cstmt.setString(2, "ERROR");
        cstmt.setString(3, "訂單支付回調失敗：簽名驗證不通過");
        cstmt.registerOutParameter(4, Types.NUMERIC); // OUT 參數
        
        cstmt.executeUpdate(); // 執行 DML 過程用 executeUpdate
        
        long newLogId = cstmt.getLong(4);
        out.println("日誌記錄成功，ID: " + newLogId);
    } catch (SQLException e) {
        out.println("調用失敗: " + e.getMessage());
        e.printStackTrace();
    }
%>

進階範例：返回 REF CURSOR 結果集

此為報表、列表頁最常見場景，存儲過程打開游標，Java 端獲取 ResultSet 迭代渲染。

Oracle 端：包規範與實現

CREATE OR REPLACE PACKAGE pkg_report AS
    TYPE ref_cursor IS REF CURSOR;
    PROCEDURE get_user_orders (
        p_user_id   IN  NUMBER,
        p_start_dt  IN  DATE,
        p_end_dt    IN  DATE,
        p_cursor    OUT ref_cursor
    );
END pkg_report;
/

CREATE OR REPLACE PACKAGE BODY pkg_report AS
    PROCEDURE get_user_orders (
        p_user_id  IN NUMBER,
        p_start_dt IN DATE,
        p_end_dt   IN DATE,
        p_cursor   OUT ref_cursor
    ) AS
    BEGIN
        OPEN p_cursor FOR
            SELECT o.order_id, o.order_no, o.total_amount, o.status, o.create_time
            FROM orders o
            WHERE o.user_id = p_user_id
              AND o.create_time BETWEEN p_start_dt AND p_end_dt
            ORDER BY o.create_time DESC;
    END;
END pkg_report;
/

JSP 端：獲取 ResultSet 並渲染表格

<%@ page import="java.sql.*, oracle.jdbc.*" %>
<%
    long userId = Long.parseLong(request.getParameter("userId"));
    java.sql.Date start = java.sql.Date.valueOf(request.getParameter("start"));
    java.sql.Date end = java.sql.Date.valueOf(request.getParameter("end"));
    
    String sql = "{call pkg_report.get_user_orders(?, ?, ?, ?)}";
    
    try (Connection conn = dataSource.getConnection();
         CallableStatement cstmt = conn.prepareCall(sql)) {
        
        cstmt.setLong(1, userId);
        cstmt.setDate(2, start);
        cstmt.setDate(3, end);
        cstmt.registerOutParameter(4, OracleTypes.CURSOR); // 關鍵：OracleTypes.CURSOR
        
        cstmt.execute();
        
        // 獲取 REF CURSOR 轉為 ResultSet
        try (ResultSet rs = (ResultSet) cstmt.getObject(4)) {
            out.println("<table border='1' cellpadding='5'>");
            out.println("<tr><th>訂單號</th><th>金額</th><th>狀態</th><th>下單時間</th></tr>");
            while (rs.next()) {
                out.println("<tr>");
                out.println("<td>" + rs.getString("order_no") + "</td>");
                out.println("<td>" + rs.getBigDecimal("total_amount") + "</td>");
                out.println("<td>" + rs.getString("status") + "</td>");
                out.println("<td>" + rs.getTimestamp("create_time") + "</td>");
                out.println("</tr>");
            }
            out.println("</table>");
        }
    } catch (SQLException e) {
        out.println("查詢異常: " + e.getMessage());
    }
%>

複雜場景：PL/SQL 記錄類型、數組、自定義類型

Oracle 支援傳遞集合（VARRAY/NESTED TABLE）與物件類型，對應 JDBC ARRAY、STRUCT，需創建對應的 Java 類實現 SQLData 或 ORAData 介面。現代應用建議改用 JSON 字串傳遞複雜結構，或在中間層組裝，降低資料庫耦合度。

效能優化與最佳實踐清單

1. 使用連接池：HikariCP 配置 dataSourceClassName=oracle.jdbc.pool.OracleDataSource，啟用 implicitCachingEnabled=true 緩存 PreparedStatement/CallableStatement。
2. 批量綁定：大量調用時使用 OraclePreparedStatement.setExecuteBatch() 或標準 addBatch()。
3. 獲取大字段：CLOB/BLOB 需用 getCharacterStream()/getBinaryStream() 流式讀取，避免記憶體溢出。
4. 關閉游標：REF CURSOR 對應的 ResultSet 必須顯式關閉，否則會導致 Oracle 端游標洩漏（ORA-01000: maximum open cursors exceeded）。
5. 異常處理：PL/SQL 端使用 RAISE_APPLICATION_ERROR(-20001, '業務錯誤') 拋出自定義錯誤碼，Java 端捕獲 SQLException.getErrorCode() 進行業務判斷。
6. 語句緩存大小：connectionProperties.setProperty("oracle.jdbc.implicitStatementCacheSize", "100")。

常見錯誤與排查

• PLS-00306: wrong number or types of arguments：檢查參數順序、個數、類型映射（特別是 DATE/TIMESTAMP、NUMBER 對應 Java 類型）。
• ORA-06550: line 1, column 7: PLS-00905: object is invalid：存儲過程編譯失效，需重新編譯 ALTER PROCEDURE ... COMPILE。
• java.sql.SQLException: Invalid column type: 2012：REF CURSOR 註冊類型錯誤，必須用 OracleTypes.CURSOR (-10) 或 Types.REF_CURSOR (2012，JDBC 4.2+)。
• 連接洩漏：確保 Connection/CallableStatement/ResultSet 在 finally 或 try-with-resources 中關閉。

架構建議：存儲過程的合理邊界

雖然 Oracle 存儲過程強大，但不建議將所有業務邏輯下沉：

• 適合：複雜報表聚合、批量數據加工、跨表一致性操作、核心計價/結算邏輯、資料庫級安全審計。
• 不適合：流程控制、字串處理、外部 API 調用、複雜業務規則（難測試、難版本控制、鎖定廠商）。

採用「厚服務層、薄資料層」架構，業務邏輯留在 Java/Spring 服務層，資料庫專注資料存取與集合運算，利用 ORM（MyBatis/JPA）管理 SQL，存儲過程作為效能優化的最後手段而非首選。

總結

JSP 調用 Oracle 存儲過程的核心在於：正確映射參數模式（IN/OUT/IN OUT/REF CURSOR）、使用 CallableStatement 配合 OracleTypes、嚴格管理資源生命週期、善用連接池語句緩存。掌握 REF CURSOR 返回結果集、自定義錯誤碼傳遞、批量綁定等進階技巧，能顯著提升企業級應用的開發效率與運行穩定性。