[JAVA100例] 第58例：JDBC 調用存儲過程——CallableStatement 實戰

本例演示使用 CallableStatement 調用 Oracle 存儲過程：傳遞輸入參數、執行過程、查詢結果表驗證效果。

核心代碼

public class JDBCSTMConn {
  private static String url, username, password;

  public Connection conn() {
    try {
      Class.forName("oracle.jdbc.driver.OracleDriver");
      return DriverManager.getConnection(url, username, password);
    } catch (Exception e) { ... return null; }
  }

  public void execute(Connection con) {
    CallableStatement toesUp = null;
    try {
      con.setAutoCommit(false);
      // 1. 準備調用：{call 過程名(?, ?...)}
      toesUp = con.prepareCall("{call p_test(?)}");
      // 2. 綁定輸入參數
      toesUp.setInt(1, 6);
      // 3. 執行
      toesUp.executeQuery();
      
      // 4. 驗證結果：查詢相關表
      Statement stmt = con.createStatement();
      ResultSet rs = stmt.executeQuery("SELECT * FROM TEST");
      while (rs.next()) {
        System.out.println(rs.getString(1) + " " + rs.getString(2));
      }
      rs.close();
    } catch (SQLException e) { System.out.println(e); 
      try { toesUp.close(); con.close(); } catch (Exception es) { System.out.println(es); }
    }
  }

  public void demo() {
    try {
      JDBCSTMConn oc = new JDBCSTMConn();
      Connection conn = oc.conn();
      oc.execute(conn);
      conn.close();
    } catch (Exception e) { System.out.println(e); }
  }

  public static void main(String[] arg) {
    if (arg.length != 3) { System.out.println("use: java JDBCSTMConn url username password"); return; }
    JDBCSTMConn oc = new JDBCSTMConn();
    oc.url = arg[0]; oc.username = arg[1]; oc.password = arg[2];
    oc.demo();
  }
}

CallableStatement 關鍵語法

參數綁定完整流程

CallableStatement cs = conn.prepareCall("{call proc(?, ?)}");
cs.setInt(1, 100);                    // IN 參數
cs.registerOutParameter(2, Types.VARCHAR);  // OUT 參數註冊類型
cs.execute();
String result = cs.getString(2);      // 獲取 OUT 參數值

Oracle 存儲過程示例

CREATE OR REPLACE PROCEDURE p_test(p_id IN NUMBER) AS
BEGIN
  INSERT INTO TEST (ID, NAME) VALUES (p_id, 'User_' || p_id);
  COMMIT;
END;
/

最佳實踐

1. 統一使用 {call ...} 標準語法，跨數據庫可移植
2. 輸出參數必須 registerOutParameter，類型對應 java.sql.Types
3. 批量調用：cs.addBatch() + cs.executeBatch()
4. 結果集處理：Oracle 存儲過程可返回 REF CURSOR，需 cs.getObject() 轉 ResultSet
5. 事務邊界：存儲過程內部提交/回滾與外部事務協調，避免嵌套事務衝突

// MyBatis Mapper
@Select("{call p_test(#{id})}")
@Options(statementType = StatementType.CALLABLE)
void callProc(@Param("id") int id);

// Spring Data JPA
@Procedure(name = "p_test")
void callProc(@Param("p_id") Integer id);