您现在的位置：程式碼之家 >> 編程語言 > >> Java 程式設計 >> Java 綜合教學

Java 調用 Oracle 存儲過程完整指南：無返回值、OUT 參數、REF CURSOR 游標三種模式

編輯：Java 綜合教學

通用調用模板

String sql = "{call PKG_NAME.PROC_NAME(?, ?, ?)}"; // ? 佔位符對應 IN/OUT/INOUT
Connection conn = dataSource.getConnection();
CallableStatement cs = conn.prepareCall(sql);
// 1. 綁定 IN 參數
cs.setInt(1, id);
cs.setString(2, name);
// 2. 註冊 OUT 參數
cs.registerOutParameter(3, Types.INTEGER);
// 3. 執行
cs.execute();
// 4. 獲取 OUT 結果
int result = cs.getInt(3);
// 5. 關閉資源
cs.close(); conn.close();

模式一：無返回值（純業務處理）

-- Oracle
CREATE OR REPLACE PROCEDURE delete_user(p_id IN NUMBER) AS
BEGIN
  DELETE FROM users WHERE id = p_id;
  COMMIT;
END;
/

// Java
cs = conn.prepareCall("{call delete_user(?)}");
cs.setLong(1, 100L);
cs.execute();

模式二：OUT 參數返回標量值

-- Oracle
CREATE OR REPLACE PROCEDURE get_user_name(p_id IN NUMBER, p_name OUT VARCHAR2) AS
BEGIN
  SELECT name INTO p_name FROM users WHERE id = p_id;
EXCEPTION WHEN NO_DATA_FOUND THEN p_name := 'NOT_FOUND';
END;
/

// Java
cs = conn.prepareCall("{call get_user_name(?, ?)}");
cs.setLong(1, 100L);
cs.registerOutParameter(2, Types.VARCHAR);
cs.execute();
String name = cs.getString(2);

模式三：REF CURSOR 返回結果集（核心難點）

Oracle 端：Package + Procedure

CREATE OR REPLACE PACKAGE user_pkg AS
  TYPE user_cur IS REF CURSOR;
  PROCEDURE query_all(p_cur OUT user_cur);
END user_pkg;
/
CREATE OR REPLACE PACKAGE BODY user_pkg AS
  PROCEDURE query_all(p_cur OUT user_cur) AS
  BEGIN
    OPEN p_cur FOR SELECT id, name, email FROM users;
  END;
END user_pkg;
/

Java 端：OracleTypes.CURSOR + ResultSet 轉換

cs = conn.prepareCall("{call user_pkg.query_all(?)}");
cs.registerOutParameter(1, OracleTypes.CURSOR); // 需引入 oracle.jdbc.OracleTypes
cs.execute();
ResultSet rs = (ResultSet) cs.getObject(1); // 強轉
while (rs.next()) {
    System.out.println(rs.getLong("id") + " | " + rs.getString("name"));
}
rs.close();

進階：INOUT 參數與複雜類型

-- INOUT 示例：累加器
CREATE OR REPLACE PROCEDURE accumulate(p_val IN OUT NUMBER) AS
BEGIN
  p_val := p_val + 10;
END;
/
// Java
cs = conn.prepareCall("{call accumulate(?)}");
cs.setInt(1, 5);
cs.registerOutParameter(1, Types.INTEGER); // 同一位置註冊 OUT
cs.execute();
int result = cs.getInt(1); // 15