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 替代存儲過程