SqlServer调用存储过程返回结果集

USE[zhuhaioa7-4]
GO
/****** Object: StoredProcedure [dbo].[proc_records] Script Date: 12/26/2014 20:31:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure[dbo].[proc_records]
as
DECLARE@map_table table(r_key varchar(100),r_value varchar(50))
DECLARE@type_id varchar(40)
DECLARE@type_name varchar(50)
DECLARE@project_id varchar(40)
DECLARE@project_name varchar(50)
DECLARE@payTypeMoney numeric(16, 2)
DECLARE@sumPayTypeMoney numeric(16, 2)
BEGIN
set@payTypeMoney= 0
set@sumPayTypeMoney= 0
--查询项目列表
DECLARE project_cursor CURSOR for select ep_id,ep_name from AB_engineeringPhase where account_id='2' open project_cursor
fetch next from project_cursor into@project_id,@project_name
while@@FETCH_STATUS= 0
begin
--查询项目支付类别
DECLARE projectType_cursor CURSOR for select id,name from T_DataItem where typeId='payType'order by sort asc
open projectType_cursor
fetch next from projectType_cursor into@type_id,@type_name
while@@FETCH_STATUS= 0
begin
--业务逻辑处理处理内层游标
--获取一个项目某一项支付类型的数据
--查询一个项目某一支付类别金额
select@payTypeMoney=sum(checkProjectPayMoney)from JiLiangZhiFu where projectId=@project_id and payType=@type_id group by payType
--一个项目某一支付类别金额存入
insert into@map_table values(@project_id+','+@type_id,@payTypeMoney)
--一个项目累计支付类别金额
set@sumPayTypeMoney=@sumPayTypeMoney+@payTypeMoney
--一个项目某一支付类别金额重置为
set@payTypeMoney= 0
fetch next from projectType_cursor into@type_id,@type_name--内层游标向下移动一行
end
--插入某一项目各个支付类别的金额合计
insert into@map_table values(@project_id,@sumPayTypeMoney)
set@sumPayTypeMoney= 0
close projectType_cursor
deallocate projectType_cursor
fetch next from project_cursor into@project_id,@project_name--内层游标处理结束后,外层游标才继续向下移动一行
end
close project_cursor
deallocate project_cursor
select*from@map_table
END
public Map getProjectPayMoneyByPayType() {
try {
return (Map)this.getHibernateTemplate().execute(
new HibernateCallback(){
public Object doInHibernate(Session session) throws HibernateException, SQLException { Connection con = session.connection();
Statement stmt = con.createStatement();
CallableStatement cs = con.prepareCall("{call proc_records}");
//ResultSet rs = stmt.executeQuery("{call proc_records}");
ResultSet rs = cs.executeQuery();
Map map = new HashMap();
while (rs.next()) {
map.put(rs.getString(1), rs.getString(2));
}
rs.close();
stmt.close();
return map;
}
}
);
} catch ( org.springframework.dao.DataAccessException e) { throw new DataAccessException(e.getMessage(),e);
}
}。