declare @sql varchar(8000)set @sql = 'select convert(char(7),cor_creat_date,120) as 日期'select @sql = @sql + ',sum(case user_se_name when '''+user_se_name+'''then 1 else 0 end) ['+user_se_name+']'from (select distinct user_se_name from cus_cor_cus join lim_user on cor_user_code=user_code) as aselect @sql = @sql+' from cus_cor_cus join lim_user on cor_user_code=user_code group by convert(char(7),cor_creat_date,120)'exec(@sql)
运行结果
日期 张三 李四
2009-02 10 12
2010-01 2 5
我想用这个结果集上面的sql怎么改成存储过程?在SSH框架中怎么调用?
public class ExecuteProceduresDaoImpl extends JdbcDaoSupport implements ExecuteProceduresDao {
public Object Call_prLS_OrderByMemberOrNotMember(final String[] parm) {
String procedureSql = "{?=call prLS_OrderByMemberOrNotMember(?,?,?,?,?,?,?,?,?,?)}";
return (Object) getJdbcTemplate().execute(procedureSql, new CallableStatementCallback() {
public Object doInCallableStatement(CallableStatement cs)
throws SQLException, DataAccessException {
int j = 2;
cs.registerOutParameter(1, Types.INTEGER);
if (parm != null) {
for (int i = 0; i < parm.length; i++) {
cs.setString(j, parm[i]);
++j;
}
}
if (cs.execute()) {
ResultSet rs = cs.getResultSet();
while (rs.next()) {
rs.getString(1);
rs.getString(2);
rs.getString(3);
}
return null;
} else {
return cs.getInt(1);
}
}
});
}}
cs.registerOutParameter(1, Types.INTEGER); 注册游标 cs.getInt(1); 获取过程的结果。 没问题的。