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
4 存储过程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; }
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 ;