Java Code

import java.sql.ResultSet;

import java.sql.SQLException;

import java.text.DateFormat;

import java.text.SimpleDateFormat;

import java.util.HashMap;

import java.util.List;

import java.util.Map;



import javax.sql.DataSource;



import oracle.jdbc.OracleTypes;



import org.springframework.dao.DataAccessException;

import org.springframework.jdbc.core.RowMapper;

import org.springframework.jdbc.core.SqlOutParameter;

import org.springframework.jdbc.core.SqlParameter;

import org.springframework.jdbc.object.StoredProcedure;







public class GetUserActionsOnV2 extends StoredProcedure {

public GetUserActionsOnV2(DataSource dataSource) {
	setDataSource(dataSource);
	setSql("GETUSERACTIONSONV2");
	final DateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
	declareParameter(new SqlOutParameter("active_rs", OracleTypes.CURSOR, new RowMapper() {
	   public Map mapRow(ResultSet rs, int rowNum)
			throws SQLException {
		   Map map = new HashMap();
		   map.put("SOEID", rs.getString("SOEID"));
		   map.put("IOIID", rs.getString("IOIID"));
		   map.put("NAME", rs.getString("NAME"));
		   map.put("VALUE", rs.getString("VALUE"));
		   map.put("DESTINATIONUSER", rs.getString("DESTINATIONUSER"));
		   map.put("ADD_TS", df.format(rs.getTimestamp("ADD_TS")));
		   map.put("UPDATE_TS", df.format(rs.getTimestamp("UPDATE_TS")));
		   return map;
	}
}));
	declareParameter(new SqlOutParameter("return_sql", OracleTypes.VARCHAR));
	
	declareParameter(new SqlParameter("V_SOEID",OracleTypes.VARCHAR));
	declareParameter(new SqlParameter("V_IOIID", OracleTypes.VARCHAR));
	declareParameter(new SqlParameter("V_NAME",OracleTypes.VARCHAR));
	declareParameter(new SqlParameter("V_VALUE",OracleTypes.VARCHAR));
	declareParameter(new SqlParameter("V_DESTINATIONUSER",OracleTypes.VARCHAR));
	
	
	compile();
}
	
public List> executeGetUserActions(Map param) throws Exception {
	Map inParameters = new HashMap();
	inParameters.put("V_SOEID", param.get("SOEID"));
	inParameters.put("V_IOIID", param.get("IOIID"));
	inParameters.put("V_NAME", param.get("NAME"));
	inParameters.put("V_VALUE", param.get("VALUE"));
	inParameters.put("V_DESTINATIONUSER", param.get("DESTINATIONUSER"));
	try {
		Map result = execute(inParameters);
		String sql = (String) result.get("return_sql");
		logger.info("GetUserActionsOnV2 sql=" + sql);
		
		List> activeList = (List>) result.get("active_rs");
		return activeList;
	} catch (DataAccessException e) {
		logger.error("Failed to GetUserActionsOnV2 !");
		throw new Exception(e);
	}
}

}

SP code

/*

#ident "%W%"

*/



CREATE OR REPLACE PROCEDURE GETUSERACTIONSONV2(

  active_rs OUT ×××××_TYPES.cur_type,

  return_sql OUT VARCHAR2,

  V_SOEID IN VARCHAR2,

  V_IOIID IN VARCHAR2,

  V_NAME IN VARCHAR2,

  V_VALUE IN VARCHAR2,

  V_DESTINATIONUSER IN VARCHAR2

)

IS



v_sql VARCHAR2(1000);



BEGIN

 v_sql := 'SELECT * FROM USER_ACTION_V2 WHERE 1 = 1 ';

 

  if V_SOEID is not null THEN

    v_sql := v_sql || 'AND (SOEID = '''|| upper(V_SOEID) || ''' OR DESTINATIONUSER = ''' || upper(V_SOEID) || ''') ';

  end if;

 

  if V_IOIID is not null THEN

  	v_sql := v_sql || 'AND IOIID =''' || V_IOIID || ''' ';

  end if;

 

  if V_NAME is not null THEN

  	v_sql := v_sql || 'AND NAME =''' || upper(V_NAME) || ''' ';

  end if;

  

  if V_VALUE is not null THEN

  	v_sql := v_sql || 'AND VALUE =''' || V_VALUE || ''' ';

  end if;



  v_sql := v_sql || 'ORDER BY ADD_TS DESC';

  

  return_sql := v_sql;

  OPEN active_rs FOR v_sql;

END;

/

注意问题：

（1）Java StoredProcedure 中定义in，out参数的顺序要与SP sql中定义的in，out参数顺序一致

declareParameter(new SqlOutParameter(“return_sql”, OracleTypes.VARCHAR));
declareParameter(new SqlParameter(“V_SOEID”,OracleTypes.VARCHAR));
declareParameter(new SqlParameter(“V_IOIID”, OracleTypes.VARCHAR));

sp sql：

return_sql OUT VARCHAR2,
V_SOEID IN VARCHAR2,
V_IOIID IN VARCHAR2,

（2）如果sp中out参数返回是cursor，必须定义RowMapper对cursor返回结果进行处理，否则get的结果就为空。

final DateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
	declareParameter(new SqlOutParameter("active_rs", OracleTypes.CURSOR, new RowMapper() {
	   public Map mapRow(ResultSet rs, int rowNum)
			throws SQLException {
		   Map map = new HashMap();
		   map.put("SOEID", rs.getString("SOEID"));
		   map.put("IOIID", rs.getString("IOIID"));
		   map.put("NAME", rs.getString("NAME"));
		   map.put("VALUE", rs.getString("VALUE"));
		   map.put("DESTINATIONUSER", rs.getString("DESTINATIONUSER"));
		   map.put("ADD_TS", df.format(rs.getTimestamp("ADD_TS")));
		   map.put("UPDATE_TS", df.format(rs.getTimestamp("UPDATE_TS")));
		   return map;
	}
}));