ssm调用后台oracle存储过程统计分析数据
笔者所在项目需要一个统计本机构近6月收入情况（分两种）、本机构下级机构收入情况的需求，数据量为百万级。
具体需求是时间、机构都不确定，可为入参。 综合考虑后决定使用后台存储过程统计。
基础表结构如下：（本功能只用到红框部分）
1.创建用于返回数据的游标：
create or replace package clf_yxfx
as
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 映射接口：
@Component public interface Yth_clfxyfxMapper extends IBaseService<TdzrsyxxModel>{ List<Map<String,Object>> getClf_yxfx(Map<String, Object> param); List<Map<String,Object>> getClf_yxfx_gro_jg(Map<String, Object> map); }
mapper 映射文件（CALLABLE表示调用存储过程，与使用sqlplus执行存储过程类似，不仅需要传入参数，还需要自定义结果集接收过程的输出。
此处定义一个map用于传参，其中返回的结果集为result，类型为CURSOR)：
<resultMap type="_HashMap" id="resultMap">
<result property="sb" column="sb"/>
<result property="pg" column="pg"/>
<result property="sz" column="sz"/>
<result property="sj" column="sj"/>
</resultMap>
<select id="getClf_yxfx" statementType="CALLABLE" parameterType="java.util.Map">
{call getClf_yxfx(
#{now,mode=IN,jdbcType=VARCHAR},
#{zgswskfj_dm,mode=IN,jdbcType=VARCHAR},
#{result,jdbcType=CURSOR,mode=OUT,javaType=ResultSet,resultMap=resultMap}
)}
</select>
<resultMap type="_HashMap" id="resultMap2">
<result property="sb" column="sb"/>
<result property="pg" column="pg"/>
<result property="swjg_dm" column="swjg_dm"/>
<result property="swjgmc" column="swjgmc"/>
</resultMap>
<select id="getClf_yxfx_gro_jg" statementType="CALLABLE" parameterType="java.util.Map">
{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}
)}
</select>
service 接口：
public interface IYth_clfxyfxService extends IBaseService<TdcrsyxxModel>{ Map<String, Object> query(Map<String, Object> paramMap); List<Map<String, Object>> querybyswjg(Map<String, Object> paramMap); }
service实现:（实现的关键是定义一个结果集去传入过程用于接收结果，调用过程后从map中取值即可）
重点实现：
Map<String, Object> map =new HashMap<String, Object>(); map.put("now",paramMap.get("now")); map.put("zgswskfj_dm",paramMap.get("zgswskfj_dm")); map.put("result", new ArrayList<Map<String,Object>>()); mapper.getClf_yxfx(map); List<Map<String,Object>> list=(List<Map<String, Object>>) map.get("result");
本业务实现（各种计算，可不看）:
@Service
public class Yth_clfxyfxServiceImpl extends BaseServiceImpl<TdcrsyxxModel> 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<String, Object> query(Map<String, Object> paramMap) {
Map<String, Object> map =new HashMap<String, Object>();
map.put("now",paramMap.get("now"));
map.put("zgswskfj_dm",paramMap.get("zgswskfj_dm"));
map.put("result", new ArrayList<Map<String,Object>>());
mapper.getClf_yxfx(map);
List<Map<String,Object>> list=(List<Map<String, Object>>) map.get("result");
List<String> sj=new ArrayList<String>();
List<String> sb=new ArrayList<String>();
List<String> pg=new ArrayList<String>();
List<Object> sb_qs_yhs=new ArrayList<Object>();
List<Object> pg_qs_yhs=new ArrayList<Object>();
List<Object> sub_add=new ArrayList<Object>();
//0 1,2 3,4 5
for(int i=0;i<list.size()-1;i++){
if(i%2==0){
Map<String,Object> m=list.get(i);
Map<String,Object> m2=list.get(i+1);
String sbsk="0";
String pgsk="0";
sj.add(m.get("sj").toString());
String sb1=m.get("sb")!=null?m.get("sb").toString():"0";
String sb2=m2.get("sb")!=null?m2.get("sb").toString():"0";
String pg1=m.get("pg")!=null?m.get("pg").toString():"0";
String pg2=m2.get("pg")!=null?m2.get("pg").toString():"0";
sbsk=m.get("sb")!=null?add(m.get("sb").toString(),sbsk,2):sbsk;
sbsk=m2.get("sb")!=null?add(m2.get("sb").toString(),sbsk,2):sbsk;
pgsk=m.get("pg")!=null?add(m.get("pg").toString(),pgsk,2):pgsk;
pgsk=m2.get("pg")!=null?add(m2.get("pg").toString(),pgsk,2):pgsk;
sub_add.add("效应增加税款："+sub(add(pg1,pg2,2),add(sb1,sb2,2),2)+"(元)");
sb_qs_yhs.add(" 其中契税:"+add(sb1,"0",2)+"(元)<br/> 其中印花税:"+add(sb2,"0",2)+"(元)");
pg_qs_yhs.add(" 其中契税:"+add(pg1,"0",2)+"(元)<br/> 其中印花税:"+add(pg2,"0",2)+"(元)");
pg.add(pgsk);
sb.add(sbsk);
}
}
map.put("sub_add", sub_add);
map.put("sb", sb);
map.put("pg",pg);
String sbmax= getMax(sb);
String pgmax= getMax(pg);
map.put("sbmax", sbmax);
map.put("pgmax", pgmax);
List<String> a=new ArrayList<String>();
a.add(pgmax);
a.add(sbmax);
String max=getMax(a);
map.put("max",max);
map.put("avglist", getSsforY(max));
map.put("sbavg", getAvg(sb));
map.put("pgavg", getAvg(pg));
map.put("sbmin", getMin(sb));
map.put("pgmin", getMin(pg));
map.put("sj", sj);
map.put("sb_qs_yhs", sb_qs_yhs);
map.put("pg_qs_yhs", pg_qs_yhs);
return map;
}
/**
* 返回6个等份的值
* @param max
* @return list
*/
private List<String> getSsforY(String max) {
Double avg=Double.parseDouble(max)/6;
List<String> b=new ArrayList<String>();
for (int i = 0; i < 7; i++) {
b.add(add("0",Double.toString(avg*i),2));
}
return b;
}
/**
* 提供精确的加法运算
*
* @param v1 被加数
* @param v2 加数
* @param scale 保留scale 位小数
* @return 两个参数的和
*/
public static String add(String v1, String v2, int scale) {
if (scale < 0) {
throw new IllegalArgumentException(
"The scale must be a positive integer or zero");
}
BigDecimal b1 = new BigDecimal(v1);
BigDecimal b2 = new BigDecimal(v2);
return b1.add(b2).setScale(scale, BigDecimal.ROUND_HALF_UP).toString();
}
/**
* 提供精确的减法运算
*
* @param v1 被减数
* @param v2 减数
* @param scale 保留scale 位小数
* @return 两个参数的差
*/
public static String sub(String v1, String v2, int scale) {
if (scale < 0) {
throw new IllegalArgumentException(
"The scale must be a positive integer or zero");
}
BigDecimal b1 = new BigDecimal(v1);
BigDecimal b2 = new BigDecimal(v2);
return b1.subtract(b2).setScale(scale, BigDecimal.ROUND_HALF_UP).toString();
}
/**
* @param list 求值list
* @return 数组最大值
*/
public static String getMax(List<String> list){
double num =Double.parseDouble(list.get(0)) ; //0为第一个数组下标
for (int i = 1; i < list.size(); i++) { //开始循环一维数组
double temp =Double.parseDouble(list.get(i)) ; //0为第一个数组下标
if (temp > num) { //循环判断数组元素
num = temp;
} //赋值给num，然后再次循环
}
return Double.toString(num);
}
/**
* @param list 求值list
* @return 数组最小值
*/
public static String getMin(List<String> list){
double num =Double.parseDouble(list.get(0)) ; //0为第一个数组下标
for (int i = 1; i < list.size(); i++) { //开始循环一维数组
double temp =Double.parseDouble(list.get(i)) ; //0为第一个数组下标
if (temp < num) { //循环判断数组元素
num = temp;
} //赋值给num，然后再次循环
}
return Double.toString(num);
}
/**
* @param list 求值list
* @return 数组平均值
*/
public static String getAvg(List<String> list){
double num =0.0; ; //0为第一个数组下标
for (int i = 0; i < list.size(); i++) { //开始循环一维数组
double temp =Double.parseDouble(list.get(i)) ; //0为第一个数组下标
num+=temp;
}
return add(Double.toString(num/list.size()),"0",2);
}
/**
* 查当前机构的下级
*/
@Override
public List<Map<String, Object>> querybyswjg(Map<String, Object> paramMap) {
Map<String, Object> map =new HashMap<String, Object>();
map.put("now",paramMap.get("now"));
map.put("zgswskfj_dm",paramMap.get("zgswskfj_dm"));
map.put("result", new ArrayList<Map<String,Object>>());
mapper.getClf_yxfx_gro_jg(map);
List<Map<String,Object>> list=(List<Map<String, Object>>) map.get("result");
List<Map<String,Object>> returnlist=new ArrayList<Map<String,Object>>();
for (Map<String, Object> m : list) {
String sb=m.get("sb")!=null?add(m.get("sb").toString(),"0",2):"0";
String pg=m.get("pg")!=null?add(m.get("pg").toString(),"0",2):"0";
String ce=sub(pg,sb,2);
Map<String,Object> m2=new HashMap<String, Object>();
String zzl=mul((!"0".equals(sb)?divide(Double.parseDouble(ce),Double.parseDouble(sb),4):0),100)+"%";
m2.put("zzl",zzl);
m2.put("sb", sb);
m2.put("pg", pg);
m2.put("ce", ce);
m2.put("swjg_dm", m.get("swjg_dm"));
m2.put("swjgmc", m.get("swjgmc"));
returnlist.add(m2);
}
return returnlist;
}
/**
* 提供精确的乘法运算。
*
* @param v1
* 被乘数
* @param v2
* 乘数
* @return 两个参数的积
*/
public static double mul(double v1, double v2) {
BigDecimal b1 = new BigDecimal(Double.toString(v1));
BigDecimal b2 = new BigDecimal(Double.toString(v2));
return b1.multiply(b2).doubleValue();
}
/**
* 提供（相对）精确的除法运算。 当发生除不尽的情况时，由scale参数指定精度，以后的数字四舍五入。
*
* @param dividend 被除数
* @param divisor 除数
* @param scale 表示表示需要精确到小数点以后几位。
* @return 两个参数的商
*/
public static Double divide(Double dividend, Double divisor, Integer scale) {
if (scale < 0) {
throw new IllegalArgumentException(
"The scale must be a positive integer or zero");
}
BigDecimal b1 = new BigDecimal(Double.toString(dividend));
BigDecimal b2 = new BigDecimal(Double.toString(divisor));
return b1.divide(b2, scale, RoundingMode.HALF_UP).doubleValue();
}
}
controller 控制层：（server实现后功能已基本完成，下面主要就是展示效果）
@RequestMapping("/query")
public void query(HttpServletRequest request,HttpServletResponse response){
Map<String, Object> paramMap = getParameterMap(request);
try {
paramMap.put("zgswskfj_dm",SqlUtil.covertTaxOrgCode(paramMap.get("swjg_dm").toString()));
Map<String,Object> 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<String, Object> paramMap = getParameterMap(request);
try {
paramMap.put("zgswskfj_dm",SqlUtil.covertTaxOrgCode(paramMap.get("swjg_dm").toString()));
List<Map<String, Object>> list =yth_clfxyfxServiceImpl.querybyswjg(paramMap);
write(response, JSON.toJSONString(list));
} catch (Exception e) {
e.printStackTrace();
logger.error("查询数据异常：" + e.getMessage());
}
}
最后放一张实现的效果图（使用到了echart插件做图表）：
