JDBC 呼叫 Oracle 預存程序完整範例：OUT 參數與 REF CURSOR

案例一：單一 OUT 參數

PL/SQL 端

CREATE OR REPLACE PROCEDURE lizhe (aa OUT VARCHAR2) IS
BEGIN
  SELECT password INTO aa FROM passport WHERE id = '123111';
END lizhe;

Java 端

public static void runProcedure() {
    try {
        Class.forName("oracle.jdbc.driver.OracleDriver").newInstance();
        Connection con = DriverManager.getConnection(
            "jdbc:oracle:thin:@192.168.0.159:1521:TOPS", "YAC3", "YAC3");
        CallableStatement cs = con.prepareCall("{call lizhe(?)}");
        cs.registerOutParameter(1, Types.VARCHAR); // 關鍵：登記 OUT 參數型別
        cs.execute();
        System.out.println("value: " + cs.getString(1));
    } catch (Exception e) { e.printStackTrace(); }
}

案例二：回傳 REF CURSOR（多筆結果集）

PL/SQL 端

CREATE OR REPLACE PACKAGE testpackage AS
  TYPE test_cursor IS REF CURSOR;
END testpackage;

CREATE OR REPLACE PROCEDURE testc(p_cursor OUT testpackage.test_cursor) IS
BEGIN
  OPEN p_cursor FOR 'SELECT * FROM tb';
END testc;

Java 端

CallableStatement proc = conn.prepareCall("{call testc(?)}");
proc.registerOutParameter(1, OracleTypes.CURSOR); // 關鍵：OracleTypes.CURSOR
proc.executeQuery();
ResultSet rs = (ResultSet) proc.getObject(1);
while (rs.next()) {
    System.out.println(rs.getString("VALUE"));
}
// 記得關閉 rs、stmt、conn

關鍵點總結

1. CallableStatement 用於呼叫預存程序。
2. registerOutParameter(index, type) 必須對應 PL/SQL 宣告的 OUT 參數型別。
3. REF CURSOR 需使用 OracleTypes.CURSOR，取得後轉型為 ResultSet 處理。
4. 資源釋放順序：ResultSet → Statement → Connection，放在 finally 區塊。