Java 調用 Oracle 存儲過程示例大全：無返回值、OUT 參數、REF CURSOR、INOUT、SYS_REFCURSOR

準備工作：建表與四類存儲過程

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

-- 1. 無返回值
CREATE OR REPLACE PROCEDURE PROCTEST (P_ID IN NUMBER, P_NAME IN VARCHAR2, P_AGE IN NUMBER) AS
BEGIN INSERT INTO T_TEST_PROCEDURE VALUES (P_ID, P_NAME, P_AGE); END;
/

-- 2. OUT 參數 (非結果集)
CREATE OR REPLACE PROCEDURE PROC_RETURNNOLIST (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;
/
CREATE OR REPLACE PROCEDURE PROC_RETURNLIST (P_CURSOR OUT TEST_PROC_PACKAGE.TESTPROCCURSOR) AS
BEGIN OPEN P_CURSOR FOR SELECT * FROM T_TEST_PROCEDURE; END;
/

-- 4. SYS_REFCURSOR (11g+ 內建，無需 Package)
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 通用連線方法

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 cs = conn.prepareCall("{call PROCTEST(?,?,?)}");
cs.setInt(1, 2); cs.setString(2, "Stephen"); cs.setInt(3, 27);
cs.execute(); cs.close();

2. OUT 參數

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

3. REF CURSOR (自定義 Package 類型)

CallableStatement cs = conn.prepareCall("{call PROC_RETURNLIST(?)}");
cs.registerOutParameter(1, OracleTypes.CURSOR);
cs.execute();
ResultSet rs = (ResultSet) cs.getObject(1);
while (rs.next()) { System.out.println(rs.getString("NAME") + " " + rs.getInt("AGE")); }
rs.close(); cs.close();

4. SYS_REFCURSOR + OUT DATE (11g+ 推薦)

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

5. INOUT 參數

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

關鍵規範總結

• 參數註冊順序：`setXxx` (IN/INOUT) → `registerOutParameter` (OUT/INOUT) → `execute()` → `getXxx` (OUT/INOUT)。
• 游標類型：自定義 Package 用 `OracleTypes.CURSOR`，內建 `SYS_REFCURSOR` 同樣用 `OracleTypes.CURSOR`。
• 結果集處理：`getObject(index)` 強轉 `ResultSet`，或 `((OracleCallableStatement)cs).getCursor(index)`。
• 資源關閉順序：`ResultSet` → `CallableStatement` → `Connection`。
• 異常處理：捕獲 `SQLException`，記錄錯誤碼 `e.getErrorCode()` 與訊息 `e.getMessage()`。