关于oracle存储过程调用问题。execute method error : DataAccessException，或者时参数个数或类型错误，等等

首先给正确的存储过程调用，写SQL跟其他SQL语句没有什么两样，不过就是注意一点，返回值的位置问题，结果今天第一次接触存储过程，
一个同事又拿来给我测试，才算是接触到了吧。
网上很多这样的例子，说实话，都是大同小异的。

下面给的例子是公司里面结合spring配置做的，还有改进的地方，暂时忍不住要把这个记录下来，所以就先写了，呵呵，希望不要见笑。

这里提醒一点的就是，注意返回值参数的问题，它必须与要去取的地方相同，当然这个是应该的，可实际上可能是想让他根据自己的需要来放置地方吧，所以出现了问题。这里希望大家注意就是了，呵呵。

import java.sql.CallableStatement; import java.sql.Connection; import java.sql.ResultSet; import java.sql.ResultSetMetaData; import java.sql.SQLException; import java.util.ArrayList; import java.util.HashMap; import java.util.List; import java.util.Map; import oracle.jdbc.driver.OracleTypes; import org.springframework.dao.DataAccessException; import org.springframework.jdbc.core.CallableStatementCallback; import org.springframework.jdbc.core.CallableStatementCreator; import org.springframework.jdbc.core.support.JdbcDaoSupport; public class BaseStatics extends JdbcDaoSupport { @SuppressWarnings("unchecked") public List<Map<String, String>> execute(String storedName, String[] params) { List<Map<String, String>> resultList = null; try { resultList = (List<Map<String, String>>) getJdbcTemplate().execute( new ProcCallableStatementCreator(storedName, params), new ProcCallableStatementCallback(params.length + 1)); } catch (DataAccessException e) { throw new RuntimeException( "execute method error : DataAccessException " + e.getMessage()); } return resultList; } } class ProcCallableStatementCreator implements CallableStatementCreator { private String storedName; private String[] params; /** * Constructs a callable statement. * * @param storedProc * The stored procedure's name. * @param params * Input parameters. * @param outResultCount * count of output result set. */ public ProcCallableStatementCreator(String storedName, String[] params) { this.params = params; this.storedName = storedName; } /** * Returns a callable statement * * @param conn * Connection to use to create statement * @return cs A callable statement */ public CallableStatement createCallableStatement(Connection conn) { StringBuffer storedProcName = new StringBuffer("call "); storedProcName.append(storedName + "("); // set output parameters storedProcName.append("?"); for (int i = 0; i < params.length; i++) { // set input parameters storedProcName.append(", "); storedProcName.append("?"); } storedProcName.append(")"); // System.out.println(storedProcName); CallableStatement cs = null; try { // set the first parameter is OracleTyep.CURSOR for oracel stored // procedure try { cs = conn.prepareCall(storedProcName.toString()); } catch (Exception e) { e.printStackTrace(); } cs.registerOutParameter(params.length + 1, OracleTypes.CURSOR); // set the sencond paramter for (int i = 0; i < params.length; i++) { cs.setObject(i + 1, params[i]); } } catch (SQLException e) { throw new RuntimeException( "createCallableStatement method Error : SQLException " + e.getMessage()); } return cs; } } class ProcCallableStatementCallback implements CallableStatementCallback { int resultLen; /** * Constructs a ProcCallableStatementCallback. */ public ProcCallableStatementCallback(int resultLen) { this.resultLen = resultLen; } /** * Returns a List(Map) collection. * * @param cs * object that can create a CallableStatement given a Connection * @return resultsList a result object returned by the action, or null */ public Object doInCallableStatement(CallableStatement cs) { List<Map> resultsMap = new ArrayList<Map>(); try { cs.execute(); ResultSet rs = (ResultSet) cs.getObject(resultLen); ResultSetMetaData rm = rs.getMetaData(); int colCount = rm.getColumnCount(); String[] names = new String[colCount]; for (int i = 0; i < names.length; i++) { names[i] = rm.getColumnName(i + 1).toUpperCase(); // System.out.println(names[i]); } while (rs.next()) { Map<String, String> rowMap = new HashMap<String, String>(); for (int i = 0; i < names.length; i++) { rowMap.put(names[i], rs.getString(names[i]) == null ? "--" : rs.getString(names[i])); } resultsMap.add(rowMap); } rs.close(); } catch (SQLException e) { throw new RuntimeException( "doInCallableStatement method error : SQLException " + e.getMessage()); } return resultsMap; 

注意这一句：cs.registerOutParameter(params.length + 1, OracleTypes.CURSOR);

说实话，很多地方，可以有优化的地方，可是按照他们的原则，能看得明白，最基本最清楚就好，所以这样也挺好的。