萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 網路程式設計 >> JSP 程式設計

JSP 調用 Oracle 存儲過程實戰:IN/OUT 參數、結果集游標與事務控制

Oracle 存儲過程基礎語法

CREATE OR REPLACE PROCEDURE p_test_insert (
    p_id   IN  NUMBER,           -- 輸入參數
    p_name IN  VARCHAR2,
    p_count OUT NUMBER          -- 輸出參數
) AS
BEGIN
    INSERT INTO test_table (id, name) VALUES (p_id, p_name);
    SELECT COUNT(*) INTO p_count FROM test_table;
    COMMIT;
END;
/

-- 返回結果集 (Ref Cursor)
CREATE OR REPLACE PROCEDURE p_query_users (
    p_dept_id IN NUMBER,
    p_cursor  OUT SYS_REFCURSOR
) AS
BEGIN
    OPEN p_cursor FOR
    SELECT id, username, email FROM users WHERE dept_id = p_dept_id ORDER BY id;
END;
/

JSP/JDBC 調用完整範例

1. 簡單 IN/OUT 參數調用

<%@ page import="java.sql.*, javax.sql.DataSource, javax.naming.*" contentType="text/html;charset=UTF-8" %>
<%
  Connection conn = null;
  CallableStatement cstmt = null;
  long newCount = 0;

  try {
      Context ctx = new InitialContext();
      DataSource ds = (DataSource) ctx.lookup("java:comp/env/jdbc/oracle");
      conn = ds.getConnection();

      String proc = "{call p_test_insert(?, ?, ?)}";
      cstmt = conn.prepareCall(proc);
      cstmt.setLong(1, System.currentTimeMillis() % 100000); // p_id
      cstmt.setString(2, "測試用戶_" + System.currentTimeMillis()); // p_name
      cstmt.registerOutParameter(3, Types.BIGINT); // p_count

      cstmt.execute();
      newCount = cstmt.getLong(3);
      out.println("調用成功,表中總記錄數:" + newCount);
  } catch (Exception e) {
      e.printStackTrace();
      out.println("錯誤:" + e.getMessage());
  } finally {
      try { if (cstmt != null) cstmt.close(); } catch (Exception ignored) {}
      try { if (conn != null) conn.close(); } catch (Exception ignored) {}
  }
%>

2. 獲取 REF CURSOR 結果集

<%@ page import="java.sql.*, oracle.jdbc.OracleTypes, javax.sql.DataSource, javax.naming.*" %>
<%
  Connection conn = null;
  CallableStatement cstmt = null;
  ResultSet rs = null;

  try {
      Context ctx = new InitialContext();
      DataSource ds = (DataSource) ctx.lookup("java:comp/env/jdbc/oracle");
      conn = ds.getConnection();

      String proc = "{call p_query_users(?, ?)}";
      cstmt = conn.prepareCall(proc);
      cstmt.setInt(1, 10); // p_dept_id
      cstmt.registerOutParameter(2, OracleTypes.CURSOR); // 關鍵:OracleTypes.CURSOR

      cstmt.execute();
      rs = (ResultSet) cstmt.getObject(2); // 強轉為 ResultSet

      out.println("<table border='1'><tr><th>ID</th><th>用戶名</th><th>郵箱</th></tr>");
      while (rs.next()) {
          out.println("<tr><td>" + rs.getLong("id") + "</td><td>" + rs.getString("username") + "</td><td>" + rs.getString("email") + "</td></tr>");
      }
      out.println("</table>");
  } catch (Exception e) {
      e.printStackTrace();
  } finally {
      try { if (rs != null) rs.close(); } catch (Exception ignored) {}
      try { if (cstmt != null) cstmt.close(); } catch (Exception ignored) {}
      try { if (conn != null) conn.close(); } catch (Exception ignored) {}
  }
%>

3. 批量調用與性能優化

// 批量綁定參數
String proc = "{call p_batch_insert(?, ?)}";
cstmt = conn.prepareCall(proc);
for (int i = 0; i < 1000; i++) {
    cstmt.setLong(1, i);
    cstmt.setString(2, "batch_" + i);
    cstmt.addBatch();
}
int[] results = cstmt.executeBatch(); // 返回每條影響行數

Oracle JDBC 關鍵配置

  • 驅動類:oracle.jdbc.OracleDriver (ojdbc11.jar for JDK 11+)
  • URL 格式:jdbc:oracle:thin:@//host:1521/service_name (Easy Connect) 或 TNS 別名
  • 連線池屬性:oracle.net.CONNECT_TIMEOUT、oracle.jdbc.ReadTimeout
  • 隱式提交行為:DDL 會隱式提交,存儲過程中 COMMIT 謹慎使用,建議由應用層控制事務

常見錯誤與排查

錯誤原因解決
ORA-06550 / PLS-00306參數數量/類型/順序不匹配核對 Java registerOutParameter 類型與 PL/SQL 定義
ORA-01000 maximum open cursors exceeded游標未關閉洩漏確保 ResultSet/CallableStatement 在 finally 關閉、配置連線池 maxOpenPreparedStatements
ClassNotFoundException: OracleTypes缺少 ojdbc jar 或版本不匹配Maven 引入 com.oracle.database.jdbc:ojdbc11
Ref Cursor 返回空存儲過程未 OPEN CURSOR 或查詢無數據檢查 PL/SQL 邏輯、參數值、權限

現代化建議

  • 使用 Spring JDBC SimpleJdbcCall 或 StoredProcedure 封裝,自動處理資源、類型映射
  • MyBatis <select statementType="CALLABLE">{call proc(#{id}, #{cursor, mode=OUT, jdbcType=CURSOR, resultMap=...})}</select>
  • 業務邏輯下沉到 Service 層,JSP/Controller 僅負責參數綁定與視圖渲染
  • 複雜報表考慮 Oracle PIPELINED FUNCTION 或 Materialized View 替代存儲過程
copyright © 萬盛學電腦網 all rights reserved