您现在的位置：程式碼之家 >> 數據庫知識 > >> Oracle 資料庫 >> Oracle 資料庫基礎

Spring Framework 呼叫 Oracle REF CURSOR 儲存程序完整指南

編輯：Oracle 資料庫基礎

Oracle REF CURSOR 特殊性

Oracle 儲存程序不直接返回 ResultSet，而是透過 OUT 參數返回 REF CURSOR。JDBC 需註冊 OracleTypes.CURSOR 並從 CallableStatement.getObject() 取得 ResultSet。

資料庫端範例

CREATE OR REPLACE PACKAGE PK_AREA_PUBLIC IS
  TYPE SEARCH_RESULT IS REF CURSOR;
  PROCEDURE AREA_SEARCH(
    V_TARGET_IN   IN  VARCHAR2,
    CUR_RESULT_OUT OUT SEARCH_RESULT
  );
END;
/

CREATE OR REPLACE PACKAGE BODY PK_AREA_PUBLIC IS
  PROCEDURE AREA_SEARCH(
    V_TARGET_IN   IN  VARCHAR2,
    CUR_RESULT_OUT OUT SEARCH_RESULT
  ) IS
    V_SQL VARCHAR2(1000);
  BEGIN
    V_SQL := 'SELECT AREA_ID, AREA_NAME FROM AREAS WHERE AREA_NAME LIKE :1';
    OPEN CUR_RESULT_OUT FOR V_SQL USING '%' || V_TARGET_IN || '%';
  END;
END;
/

Spring StoredProcedure 實作

public class AreaSearchProcedure extends StoredProcedure {
  
  private static final String SQL = "PK_AREA_PUBLIC.AREA_SEARCH";
  private final List> results = new ArrayList<>();

  public AreaSearchProcedure(DataSource ds) {
    setDataSource(ds);
    setFunction(false);
    setSql(SQL);
    
    // 宣告參數
    declareParameter(new SqlParameter("V_TARGET_IN", Types.VARCHAR));
    declareParameter(new SqlOutParameter("CUR_RESULT_OUT", OracleTypes.CURSOR, 
      new RowMapperResultSetExtractor<>(new RowMapper>() {
        @Override
        public Map mapRow(ResultSet rs, int rowNum) throws SQLException {
          ResultSetMetaData meta = rs.getMetaData();
          int cols = meta.getColumnCount();
          Map row = new HashMap<>(cols);
          for (int i = 1; i <= cols; i++) {
            row.put(meta.getColumnName(i), rs.getObject(i));
          }
          return row;
        }
      })));
    compile();
  }

  public List> execute(String target) {
    Map inParams = new HashMap<>();
    inParams.put("V_TARGET_IN", target);
    Map out = super.execute(inParams);
    return results; // 由 RowMapperResultSetExtractor 填充
  }
  
  // 內部類別：將 ResultSet 轉為 List
  private static class RowMapperResultSetExtractor implements ResultSetExtractor>> {
    private final RowMapper> rowMapper;
    private final List> results = new ArrayList<>();
    
    RowMapperResultSetExtractor(RowMapper> rm) { this.rowMapper = rm; }
    
    @Override
    public List> extractData(ResultSet rs) throws SQLException {
      int rowNum = 0;
      while (rs.next()) {
        results.add(rowMapper.mapRow(rs, rowNum++));
      }
      return results;
    }
  }
}

呼叫端使用

DataSource ds = ...; // 取得 DataSource
AreaSearchProcedure proc = new AreaSearchProcedure(ds);
List> rows = proc.execute("Taipei");

for (Map row : rows) {
  System.out.println(row.get("AREA_ID") + " => " + row.get("AREA_NAME"));
}