Oracle SP 返回游标

八阿哥一生之敌

已于 2023-04-21 15:40:38 修改

阅读量262

点赞数

文章标签： oracle 数据库 sql

于 2023-04-21 15:36:42 首次发布

本文链接：https://blog.youkuaiyun.com/istruth/article/details/130290121

版权

文章展示了如何在Oracle中创建一个存储过程，该过程接受一个包含逗号分隔值的字符串，动态构建SQL查询并返回游标。然后，使用Spring的JdbcTemplate在Java中调用这个存储过程，解析返回的结果集。方法包括设置输入参数，注册输出游标，以及从结果集中提取数据。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

代码demo

Oracle 循环分割字符串
Oracle SP返回游标
Oracle 游标动态SQL查询
JdbcTemplate执行并解析SP游标返回结果集

SP

create or replace procedure sp_xxx (i_d in varchar2, i_b in varchar2, i_index in varchar2 ,ref_cur out sys_refcursor) as
   v_sql varchar2(4000);
  v_str VARCHAR2(1024);
  v_pos NUMBER;
  v_index VARCHAR2(100);
begin
  

  v_sql := ' select '||i_d ||','||i_b ;
  v_str := i_index;
  
  
  
  LOOP
    v_pos := INSTR(v_str, ',');
    EXIT WHEN v_pos = 0;
    v_index := SUBSTR(v_str, 1, v_pos-1);
    v_sql := v_sql ||','||' sum('||v_index||') as '||v_index;
    v_str := SUBSTR(v_str, v_pos+1);
  END LOOP;
  -- 拼接最后一段
  v_sql := v_sql ||','||' sum('||v_str||') as '||v_str || ' from table_a group by ' ||i_d ||','||i_b ;
   
  open ref_cur for v_sql;
end;

入参为逗号分割字符串
出参为游标

JDBC执行并获取数据

@Test
    void jdbcTemplateExecMySQL() {
        // Define the input parameter as a comma-separated string
        String d= "d1,d2,d3";
        String b= "b1,b2";
        String i= "index1,index2";


// Define the SQL query to call the stored procedure
        String sql = "{ call sp_xxx(?, ?, ?, ?) }";

// Create a JdbcTemplate object
        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);


// Call the stored procedure using the JdbcTemplate object
        // Define a SqlRowSetResultSetExtractor to extract the result set as a SqlRowSet

// Call the stored procedure using the JdbcTemplate object
        List<Map<String, Object>> mapList = (List<Map<String, Object>>) jdbcTemplate.execute(new PreparedStatementCreator() {
            @Override
            public PreparedStatement createPreparedStatement(Connection conn) throws SQLException {

                CallableStatement callableStatement = conn.prepareCall(sql);
                callableStatement.setString(1, d);
                callableStatement.setString(2, b);
                callableStatement.setString(3, i);
                callableStatement.registerOutParameter(4, OracleTypes.CURSOR);
                return callableStatement;
            }
        }, new PreparedStatementCallback<Object>() {
            @Override
            public List<Map<String, Object>> doInPreparedStatement(PreparedStatement ps) throws SQLException, DataAccessException {
                List<Map<String, Object>> resultListMap = new ArrayList<>();
                ps.execute();
                ResultSet resultSet = (ResultSet) ((CallableStatement) ps).getObject(4);
                boolean fetchedColumnMetaInfo = true;
                List<String> columns = new ArrayList<>();
                while (resultSet.next()) {
                    Map<String, Object> rowMap = new HashMap<>();
                    if (fetchedColumnMetaInfo) {
                        ResultSetMetaData resultSetMetaData = resultSet.getMetaData();
                        for (int i = 1; i <= resultSetMetaData.getColumnCount(); i++) {
                            columns.add(resultSetMetaData.getColumnName(i).toUpperCase());
                        }
                        fetchedColumnMetaInfo = false;
                    }
                    for (int i = 1; i <= columns.size(); i++) {
                        rowMap.put(columns.get(i - 1), resultSet.getObject(i));
                    }
                    resultListMap.add(rowMap);
                }
                return resultListMap;
            }
        });
    }