笔者所在项目需要一个统计本机构近6月收入情况（分两种）、本机构下级机构收入情况的需求，数据量为百万级。

具体需求是时间、机构都不确定，可为入参。 综合考虑后决定使用后台存储过程统计。

基础表结构如下：（本功能只用到红框部分）

1.创建用于返回数据的游标：

create or replace package clf_yxfxas type  type_cursor is ref cursor;end clf_yxfx;

 

2.统计本机构近6月收入情况（分契税和印花税，其中入参now为传入的年月，zgswskfj_dm为机构代码，type_cursor为返回结果集。

由于几个月份的数据在表中只占小部分，故使用中间表先单独存储需要的数据，字段（zgswskfj_dm）是一个包含子代码的机构树。

使用prior 进行遍历）:

create or replace procedure getClf_yxfx(now varchar2,zgswskfj_dm varchar2,rs out clf_yxfx.type_cursor) is  sqlText varchar2(5000);  format varchar2(10):='yyyy-mm'; begin   sqlText :='with temp as(     select t.sbssk,t.pgycsk,t.zgswskfj_dm,t.skssqq,t.skssqz,t.zsxmdm from YTH_SYMXB_FCSY t, yth_fc_jbxxb t1  where t.fcuuid = t1.uuid '     || 'and t.skssqq > add_months(to_date('|| '''' || now || '''' ||','|| '''' || format || '''' ||'),-6)'     || 'and t.skssqz <= add_months(to_date('|| '''' || now || '''' ||','|| '''' || format || '''' ||'),1)'     || 'and zsxmdm in (10119,10111) and trim(t.zgswskfj_dm) in(select trim(swjg_dm)'     || 'from dm_gy_swjg START WITH trim(swjg_dm) = '|| zgswskfj_dm ||' CONNECT BY PRIOR trim(swjg_dm) = trim(sjswjg_dm)))'     --前五个月契税     || 'select SUM(t.sbssk) as sb,sum(t.pgycsk) as pg,1 as sz,'     || 'to_char(add_months(to_date('|| '''' || now || '''' ||','|| '''' || format || '''' ||'),-5), '|| '''' || format || '''' ||') as sj '     || ' from temp t where t.skssqq >=add_months(to_date('|| '''' || now || '''' ||','|| '''' || format || '''' ||'),-5)'     || 'and t.skssqz < add_months(to_date('|| '''' || now || '''' ||','|| '''' || format || '''' ||'),-4) and zsxmdm=10119 union all '     --前五个月印花税     || 'select SUM(t.sbssk) as sb,sum(t.pgycsk) as pg,2 as sz,'     || 'to_char(add_months(to_date('|| '''' || now || '''' ||','|| '''' || format || '''' ||'),-5), '|| '''' || format || '''' ||') as sj '     || ' from temp t where t.skssqq >=add_months(to_date('|| '''' || now || '''' ||','|| '''' || format || '''' ||'),-5)'     || 'and t.skssqz < add_months(to_date('|| '''' || now || '''' ||','|| '''' || format || '''' ||'),-4) and zsxmdm=10111 union all '      --前四个月契税     || 'select SUM(t.sbssk) as sb,sum(t.pgycsk) as pg,1 as sz,'     || 'to_char(add_months(to_date('|| '''' || now || '''' ||','|| '''' || format || '''' ||'),-4), '|| '''' || format || '''' ||') as sj '     || ' from temp t where t.skssqq >=add_months(to_date('|| '''' || now || '''' ||','|| '''' || format || '''' ||'),-4)'     || 'and t.skssqz < add_months(to_date('|| '''' || now || '''' ||','|| '''' || format || '''' ||'),-3) and zsxmdm=10119 union all '     --前四个月印花税     || 'select SUM(t.sbssk) as sb,sum(t.pgycsk) as pg,2 as sz,'     || 'to_char(add_months(to_date('|| '''' || now || '''' ||','|| '''' || format || '''' ||'),-4), '|| '''' || format || '''' ||') as sj '     || ' from temp t where t.skssqq >=add_months(to_date('|| '''' || now || '''' ||','|| '''' || format || '''' ||'),-4)'     || 'and t.skssqz < add_months(to_date('|| '''' || now || '''' ||','|| '''' || format || '''' ||'),-3) and zsxmdm=10111 union all '    --前三     || 'select SUM(t.sbssk) as sb,sum(t.pgycsk) as pg,1 as sz,'     || 'to_char(add_months(to_date('|| '''' || now || '''' ||','|| '''' || format || '''' ||'),-3), '|| '''' || format || '''' ||') as sj '     || ' from temp t where t.skssqq >=add_months(to_date('|| '''' || now || '''' ||','|| '''' || format || '''' ||'),-3)'     || 'and t.skssqz < add_months(to_date('|| '''' || now || '''' ||','|| '''' || format || '''' ||'),-2) and zsxmdm=10119 union all '     --前三     || 'select SUM(t.sbssk) as sb,sum(t.pgycsk) as pg,2 as sz,'     || 'to_char(add_months(to_date('|| '''' || now || '''' ||','|| '''' || format || '''' ||'),-3), '|| '''' || format || '''' ||') as sj '     || ' from temp t where t.skssqq >=add_months(to_date('|| '''' || now || '''' ||','|| '''' || format || '''' ||'),-3)'     || 'and t.skssqz < add_months(to_date('|| '''' || now || '''' ||','|| '''' || format || '''' ||'),-2) and zsxmdm=10111 union all '    --前二     || 'select SUM(t.sbssk) as sb,sum(t.pgycsk) as pg,1 as sz,'     || 'to_char(add_months(to_date('|| '''' || now || '''' ||','|| '''' || format || '''' ||'),-2), '|| '''' || format || '''' ||') as sj '     || ' from temp t where t.skssqq >=add_months(to_date('|| '''' || now || '''' ||','|| '''' || format || '''' ||'),-2)'     || 'and t.skssqz < add_months(to_date('|| '''' || now || '''' ||','|| '''' || format || '''' ||'),-1) and zsxmdm=10119  union all '     --前二     || 'select SUM(t.sbssk) as sb,sum(t.pgycsk) as pg,2 as sz,'     || 'to_char(add_months(to_date('|| '''' || now || '''' ||','|| '''' || format || '''' ||'),-2), '|| '''' || format || '''' ||') as sj '     || ' from temp t where t.skssqq >=add_months(to_date('|| '''' || now || '''' ||','|| '''' || format || '''' ||'),-2)'     || 'and t.skssqz < add_months(to_date('|| '''' || now || '''' ||','|| '''' || format || '''' ||'),-1) and zsxmdm=10111  union all '       --前一     || 'select SUM(t.sbssk) as sb,sum(t.pgycsk) as pg,1 as sz,'     || 'to_char(add_months(to_date('|| '''' || now || '''' ||','|| '''' || format || '''' ||'),-1), '|| '''' || format || '''' ||') as sj '     || ' from temp t where t.skssqq >=add_months(to_date('|| '''' || now || '''' ||','|| '''' || format || '''' ||'),-1)'     || 'and t.skssqz < to_date('|| '''' || now || '''' ||','|| '''' || format || '''' ||') and zsxmdm=10119  union all '     --前一     || 'select SUM(t.sbssk) as sb,sum(t.pgycsk) as pg,2 as sz,'     || 'to_char(add_months(to_date('|| '''' || now || '''' ||','|| '''' || format || '''' ||'),-1), '|| '''' || format || '''' ||') as sj '     || ' from temp t where t.skssqq >=add_months(to_date('|| '''' || now || '''' ||','|| '''' || format || '''' ||'),-1)'     || 'and t.skssqz < to_date('|| '''' || now || '''' ||','|| '''' || format || '''' ||') and zsxmdm=10111  union all '     --当前月份申报契税     || 'select SUM(t.sbssk) as sb,sum(t.pgycsk) as pg,1 as sz,'     || 'to_char(to_date('|| '''' || now || '''' ||','|| '''' || format || '''' ||'), '|| '''' || format || '''' ||') as sj '     || ' from temp t where t.skssqq >=to_date('|| '''' || now || '''' ||','|| '''' || format || '''' ||')'     || 'and t.skssqz < add_months(to_date('|| '''' || now || '''' ||','|| '''' || format || '''' ||'),1) and zsxmdm=10119 union all '     --当前月份申报印花税     || 'select SUM(t.sbssk) as sb,sum(t.pgycsk) as pg,2 as sz,'     || 'to_char(to_date('|| '''' || now || '''' ||','|| '''' || format || '''' ||'), '|| '''' || format || '''' ||') as sj '     || ' from temp t where t.skssqq >=to_date('|| '''' || now || '''' ||','|| '''' || format || '''' ||')'     || 'and t.skssqz < add_months(to_date('|| '''' || now || '''' ||','|| '''' || format || '''' ||'),1) and zsxmdm=10111 ';            open rs for sqlText;end getClf_yxfx;

 

3.统计本月下级机构收入（不分契税、印花税，其中入参now为传入的年月，zgswskfj_dm为机构代码，type_cursor为返回结果集）

create or replace procedure getClf_yxfx_gro_jg(now varchar2,zgswskfj_dm varchar2,rs out clf_yxfx.type_cursor) is  sqlText varchar2(32760) :='';  format varchar2(10):='yyyy-mm';  v_cur   sys_refcursor;  v_swjg_dm varchar2(64);  v_swjgmc  varchar2(64);  v_yxbz    varchar2(2):='Y'; begin   sqlText :='with temp as(select t.sbssk as sb,t.pgycsk as pg,trim(t.zgswskfj_dm) as zgswskfj_dm from YTH_SYMXB_FCSY t, yth_fc_jbxxb t1 where t.fcuuid = t1.uuid '           || ' and t.skssqz > to_date('|| '''' || now || '''' ||','|| '''' || format || '''' ||')'           || ' and t.skssqz <= add_months(to_date('|| '''' || now || '''' ||','|| '''' || format || '''' ||'),1)'           || ' and (t.zsxmdm = 10119 or t.zsxmdm = 10111)'           || ' And Exists (Select 1 From (Select t123.swjg_dm From dm_gy_swjg t123 where  t123.swjgbz=0'           || ' and trim(t123.yxbz)='|| '''' || v_yxbz || '''' ||' Start With trim(t123.swjg_dm) ='|| zgswskfj_dm ||'  Connect By Prior trim(t123.swjg_dm) =trim(t123.sjswjg_dm)) ttt'           || ' Where trim(ttt.swjg_dm) = trim(t.zgswskfj_dm)))';              open v_cur for  select trim(t.swjg_dm),t.swjgmc                   from dm_gy_swjg t where trim(t.sjswjg_dm)= zgswskfj_dm                   and t.swjgbz='0' and trim(t.yxbz)='Y';    loop      fetch v_cur into v_swjg_dm,v_swjgmc;      exit when v_cur%notfound;      sqlText :=sqlText || 'select sum(t.sb) as sb,sum(t.pg) as pg,'      || '''' || v_swjg_dm || '''' || ' as swjg_dm,'|| '''' || v_swjgmc || '''' || ' as swjgmc from temp t'      || ' where 1=1'      || ' And Exists (Select 1 From (Select t123.swjg_dm From dm_gy_swjg t123 where  t123.swjgbz=0'      || ' and trim(t123.yxbz)='|| '''' || v_yxbz || '''' ||' Start With trim(t123.swjg_dm) ='|| v_swjg_dm ||'  Connect By Prior trim(t123.swjg_dm) =trim(t123.sjswjg_dm)) ttt'      || ' Where trim(ttt.swjg_dm) = trim(t.zgswskfj_dm)) union all ';    end loop;  sqlText :=substr(sqlText,0,instr(sqlText,'union all ',-1)-1);  open rs for sqlText;end getClf_yxfx_gro_jg;

 

5.过程编写好后，使用oracle 自带的sqlplus 对过程进行测试如下：

:定义输出结果集 var r_cur refcursor

:执行 exec getclf_yxfx_gro_jg('2017-08','25201000000',:r_cur);

:查看输出结果 print :r_cur

 

6.编写mybatis 映射文件调用过程：

mapper 映射接口：

@Componentpublic interface Yth_clfxyfxMapper extends IBaseService
     
      {        List
      
     

mapper 映射文件（CALLABLE表示调用存储过程，与使用sqlplus执行存储过程类似，不仅需要传入参数，还需要自定义结果集接收过程的输出。

此处定义一个map用于传参，其中返回的结果集为result，类型为CURSOR)：

             
              
              
              
          
                     {call getClf_yxfx(                #{now,mode=IN,jdbcType=VARCHAR},                #{zgswskfj_dm,mode=IN,jdbcType=VARCHAR},                #{result,jdbcType=CURSOR,mode=OUT,javaType=ResultSet,resultMap=resultMap}            )}            
             
              
              
              
          
                     {call getClf_yxfx_gro_jg(                #{now,mode=IN,jdbcType=VARCHAR},                #{zgswskfj_dm,mode=IN,jdbcType=VARCHAR},                #{result,jdbcType=CURSOR,mode=OUT,javaType=ResultSet,resultMap=resultMap2}            )}    

 service 接口：

public interface IYth_clfxyfxService extends IBaseService
     
      {    Map
      
        query(Map
       
         paramMap);    List
        
       
      
     

service实现:（实现的关键是定义一个结果集去传入过程用于接收结果，调用过程后从map中取值即可）

重点实现：

Map
     
       map =new HashMap
      
       ();        map.put("now",paramMap.get("now"));        map.put("zgswskfj_dm",paramMap.get("zgswskfj_dm"));        map.put("result", new ArrayList
       
      
     

 本业务实现（各种计算，可不看）:

@Servicepublic class Yth_clfxyfxServiceImpl extends BaseServiceImpl
     
       implements IYth_clfxyfxService{	@Resource	private Yth_clfxyfxMapper mapper;	/**	 * 数据格式	 * 2017-01 1 1241 1242	 * 2017-01 2 1321 1312	 * 2017-02 1 1231 1322	 * 2017-02 2 1512 1241	 */	@Override	public Map
      
        query(Map
       
         paramMap) {				Map
        
          map =new HashMap
         
          ();		map.put("now",paramMap.get("now"));		map.put("zgswskfj_dm",paramMap.get("zgswskfj_dm"));		map.put("result", new ArrayList
          
         
        
       
      
     

controller 控制层：（server实现后功能已基本完成，下面主要就是展示效果）

@RequestMapping("/query")    public void query(HttpServletRequest request,HttpServletResponse response){        Map
     
       paramMap = getParameterMap(request);        try {            paramMap.put("zgswskfj_dm",SqlUtil.covertTaxOrgCode(paramMap.get("swjg_dm").toString()));                        Map
      
        returnMap =yth_clfxyfxServiceImpl.query(paramMap);                        write(response, JSON.toJSONString(returnMap));        } catch (Exception e) {            logger.error("查询数据异常：" + e.getMessage());        }    }        @RequestMapping("/query2")    public void query2(HttpServletRequest request,HttpServletResponse response){        Map
       
         paramMap = getParameterMap(request);        try {            paramMap.put("zgswskfj_dm",SqlUtil.covertTaxOrgCode(paramMap.get("swjg_dm").toString()));                        List
        
       
      
     

最后放一张实现的效果图（使用到了echart插件做图表）：