mybatis 调用oracle 存储过程并返回结果集

1. xml文件

<select id="findMassiveWaterList" statementType="CALLABLE">	 
            {call cassify_massive_water_bill(
              #{startMonth,mode=IN},
              #{endMonth,mode=IN},
              #{waterProperty,mode=IN},
              #{avgYsl,mode=IN},
              #{monthCount,mode=IN},
              #{bfrper,mode=IN},
              #{mismfid,mode=IN},     
              #{out_return,jdbcType=CURSOR,mode=OUT,javaType=ResultSet,resultMap=cursorMap})}    
      
</select>

<resultMap id="cursorMap" type="ClassifyMassiveWater" >
	     <result column="miid" property="miid" />  
	     <result column="smmibooktip" property="smmibooktip"  />  
	     <result column="miname" property="miname" />  
	     <result column="mialias" property="mialias"  />  
	     <result column="miadr" property="miadr" />  
	     <result column="ciconnecttel" property="ciconnecttel" />  
		 <result column="ciconnectmtel" property="ciconnectmtel" />  
	     <result column="avgYsl" property="avgYsl"  />  
	     <result column="micaliber" property="micaliber" />  
	     <result column="mipfid" property="mipfid"  />  
	     <result column="mipfidText" property="mipfidText" />  
	     <result column="rasl" property="rasl"/>  
</resultMap >  

2. dao

	



public void findMassiveWaterList(Map map);


3.service





public Page<ClassifyMassiveWater> findMassiveWaterList(Page<ClassifyMassiveWater> page, ClassifyMassiveWater classifyMassiveWater){
		Map<String ,Object> map=new HashMap<String, Object>();
		map.put("startMonth", classifyMassiveWater.getStartMonth());
		map.put("endMonth", classifyMassiveWater.getEndMonth());
		map.put("waterProperty", classifyMassiveWater.getWaterProperty());
		map.put("avgYsl", classifyMassiveWater.getAvgYsl());
		map.put("monthCount", classifyMassiveWater.getMonthCount());
		map.put("bfrper", classifyMassiveWater.getBfrper());
		map.put("mismfid", classifyMassiveWater.getMismfid());
		map.put("out_return", new ArrayList<ClassifyMassiveWater>());
		dao.findMassiveWaterList(map);
		List<ClassifyMassiveWater> list =(List<ClassifyMassiveWater>) map.get("out_return");
		page.setList(list);
		return page;
	}
4 存储过程

create or replace procedure 
  (
   i_startmonth in varchar2,
   i_endmonth in varchar2,
   i_water_property in varchar2,
   i_avg_ysl  in number,
   i_month_count  in number,
   i_bfrper in varchar2,
   i_dept_no in varchar2,
   
   out_return out sys_refcursor
   )
is
v_total_ysl number(13,4);
begin
  v_total_ysl := i_avg_ysl*i_month_count;

    ...
   open out_return for
   select m.miid,s.smmibooktip,m.miname,m.mialias,m.miadr,
			    c.ciconnecttel,c.ciconnectmtel,trunc(a.total_ysl/i_month_count,2)as "avgYsl",m.micaliber,m.mipfid,
          fn_get_priceFrameType(a.rlmiid) mipfidText,
          FN_GET_RATIFY(a.rlmiid) rasl
   from temp_am a,fm_bookframe b, fm_sortmeter s, fm_meterinfo m,fm_custinfo c
   where s.smbfid=b.bfid and
        s.smmiid=a.rlmiid and
        a.rlmiid=m.miid and
        m.micid=c.ciid and
        m.mismfid=i_dept_no
        and b.bfrper=i_bfrper
        ORDER BY m.miid desc ;
end ;