Java 調用 Oracle 存儲過程完整實戰指南 (IN/OUT/REF CURSOR/INOUT)

準備工作：建立測試表與三類存儲過程

CREATE TABLE T_TEST_PROCEDURE (
  ID NUMBER(19) PRIMARY KEY,
  NAME VARCHAR2(40),
  AGE NUMBER(3)
);

-- 1. 無返回值 (純執行 DML)
CREATE OR REPLACE PROCEDURE PROC_TEST (
  P_ID IN NUMBER,
  P_NAME IN VARCHAR2,
  P_AGE IN NUMBER
) AS
BEGIN
  INSERT INTO T_TEST_PROCEDURE (ID, NAME, AGE) VALUES (P_ID, P_NAME, P_AGE);
END;
/

-- 2. 有 OUT 參數 (非結果集)
CREATE OR REPLACE PROCEDURE PROC_RETURN_NOLIST (
  P_ID IN NUMBER,
  P_NAME OUT VARCHAR2,
  P_AGE OUT NUMBER
) AS
BEGIN
  SELECT NAME, AGE INTO P_NAME, P_AGE FROM T_TEST_PROCEDURE WHERE ID = P_ID;
END;
/

-- 3. 返回結果集 (需 Package 定義 REF CURSOR)
CREATE OR REPLACE PACKAGE TEST_PROC_PACKAGE AS
  TYPE TESTPROCCURSOR IS REF CURSOR;
END TEST_PROC_PACKAGE;
/
CREATE OR REPLACE PROCEDURE PROC_RETURN_LIST (
  P_CURSOR OUT TEST_PROC_PACKAGE.TESTPROCCURSOR
) AS
BEGIN
  OPEN P_CURSOR FOR SELECT * FROM T_TEST_PROCEDURE;
END;
/

Java 端通用連線工具類

public Connection getConnection() {
    try {
        Class.forName("oracle.jdbc.driver.OracleDriver");
        return DriverManager.getConnection(
            "jdbc:oracle:thin:@//133.96.40.82:1521/ora92", "icdmain41", "icd");
    } catch (Exception e) { e.printStackTrace(); return null; }
}

三種調用模式完整代碼

1. 調用無返回值過程 (IN 參數)

CallableStatement cstmt = conn.prepareCall("{call PROC_TEST(?,?,?)}");
cstmt.setInt(1, 2);
cstmt.setString(2, "Stephen");
cstmt.setInt(3, 27);
cstmt.execute();
cstmt.close();

2. 調用帶 OUT 參數過程

CallableStatement cstmt = conn.prepareCall("{call PROC_RETURN_NOLIST(?,?,?)}");
cstmt.setInt(1, 3);
cstmt.registerOutParameter(2, Types.VARCHAR);
cstmt.registerOutParameter(3, Types.INTEGER);
cstmt.execute();
String name = cstmt.getString(2);
int age = cstmt.getInt(3);
cstmt.close();

3. 調用返回 REF CURSOR 結果集

CallableStatement cstmt = conn.prepareCall("{call PROC_RETURN_LIST(?)}");
cstmt.registerOutParameter(1, OracleTypes.CURSOR);  // 需 import oracle.jdbc.OracleTypes
cstmt.execute();
ResultSet rs = (ResultSet) cstmt.getObject(1);
// 或: ResultSet rs = ((OracleCallableStatement)cstmt).getCursor(1);
while (rs.next()) {
    System.out.println("Name: " + rs.getString("NAME") + ", Age: " + rs.getInt("AGE"));
}
rs.close();
cstmt.close();

進階範例：IN + OUT DATE + OUT CURSOR 混合

-- Oracle 端
CREATE OR REPLACE PROCEDURE P_TGETSPECIALNO (
  CALLERTYPE IN VARCHAR2,
  DATETIME OUT DATE,
  C_SPECIALNO OUT SYS_REFCURSOR
) AS
BEGIN
  DATETIME := SYSDATE;
  OPEN C_SPECIALNO FOR SELECT * FROM T_SCEJUDGESPECIALCUSTOMER T WHERE T.SPECIALNO LIKE CALLERTYPE || '%';
END;
/

-- Java 端
CallableStatement cstmt = conn.prepareCall("{call P_TGETSPECIALNO(?,?,?)}");
cstmt.setString(1, "1342636");
cstmt.registerOutParameter(2, OracleTypes.DATE);
cstmt.registerOutParameter(3, OracleTypes.CURSOR);
cstmt.execute();
String dbDate = cstmt.getString(2);  // 或 getDate()
ResultSet rs = (ResultSet) cstmt.getObject(3);
while (rs.next()) { System.out.println(rs.getString("SPECIALNO")); }
rs.close(); cstmt.close();

INOUT 參數調用模式

-- Oracle: CREATE OR REPLACE PROCEDURE LIST_EARLY_DEATHS (P_AGE IN OUT NUMBER) ...
-- Java:
CallableStatement cstmt = conn.prepareCall("{call LIST_EARLY_DEATHS(?)}");
cstmt.setInt(1, 20);           // 設定 IN 值
cstmt.registerOutParameter(1, Types.INTEGER);  // 註冊 OUT
cstmt.execute();
int result = cstmt.getInt(1);  // 取得 OUT 值
cstmt.close();

關鍵注意事項

• 註冊順序：setXxx (IN/INOUT) → registerOutParameter (OUT/INOUT) → execute → getXxx (OUT/INOUT)。
• REF CURSOR：必須使用 OracleTypes.CURSOR，取回後轉型為 ResultSet 處理。
• Package 定義：返回游標的過程**必須**在 Package 中預先定義 TYPE ... IS REF CURSOR (Oracle 9i/10g) 或直接使用內建 SYS_REFCURSOR (11g+)。
• 關閉資源：ResultSet → CallableStatement → Connection 順序關閉，防止游標洩漏。