mybatis注解方式调用存储过程

妖狐艹你老母 2022-05-18 08:23 204阅读 0赞

1.建立存储过程：

CREATE DEFINER=`root`@`%` PROCEDURE `p_dmk_fsyx`(v_month VARCHAR(6),v_type VARCHAR(2),OUT result INT(2))
    BEGIN
    IF @v_type=1 THEN
    delete from dmk_fsyx_zxy_rpt where  month_id=v_month;
    insert into dmk_fsyx_zxy_rpt
      (prov_name,
       oper_code,
       oper_name,
       channel_id,
       info_fees,
       dgp_fees,
       return_ratio,
       sett_fees,
       month_id)
      select prov_name,
             oper_code,
             oper_name,
             channel_id,
             info_fees,
             dgp_fees,
             return_ratio,
             sett_fees,
             month_id
        from dmk_fsyx_zxy a
        join (select * from dim_fsyx_oper_type where month_id = v_month) b
          on a.oper_code = b.oper_code
         and a.busi_type = b.busi_type
        join (select * from dim_fsyx_ch_type where month_id = v_month) c
          on a.prov_name = c.prov_name
         and a.channel_id = c.channel_id;
    elseIF @v_type=2 THEN
    delete from dmk_fsyx_bcxyyj_rpt where  month_id=v_month;
    insert into dmk_fsyx_bcxyyj_rpt
      (prov_name,
       oper_code,
       oper_name,
       busi_type,
       channel_id,
       order_month,
       xf_month,
       info_fees,
       fh_month,
       return_ratio,
       sett_info_fees,
       month_id)
      select a.prov_name,
             a.oper_code,
             a.oper_name,
             a.busi_type,
             a.channel_id,
             a.order_month,
             a.xf_month,
             a.info_fees,
             a.return_cycle,
             b.return_ratio,
             a.info_fees * b.return_ratio,
             a.month_id
        from (select prov_name,
                     oper_code,
                     oper_name,
                     busi_type,
                     channel_id,
                     order_month,
                     xf_month,
                     info_fees,
                     PERIOD_DIFF(xf_month, order_month) return_cycle,
                     month_id
                from dmk_fsyx_bcxyyj
               where month_id = v_month) a
        join (select * from dim_business_model where month_id = v_month) b
          on a.busi_type = b.busi_type
         and a.prov_name = b.prov_name
         and a.return_cycle = b.return_cycle
         and a.order_month between b.start_time and b.end_time
        join (select * from dim_fsyx_oper_type where month_id = v_month) c
          on a.oper_code = c.oper_code
         and a.busi_type = c.busi_type
        join (select * from dim_fsyx_ch_type where month_id = v_month) d
          on a.prov_name = d.prov_name
         and a.channel_id = d.channel_id;
    delete from dmk_fsyx_bcxyhj_rpt where  month_id=v_month;
    insert into dmk_fsyx_bcxyhj_rpt
      (prov_name,
       oper_code,
       oper_name,
       channel_id,
       order_month,
       xf_month,
       dgp_fees,
       fh_month,
       return_ratio,
       sett_dgp_fees,
       month_id)
      select a.prov_name,
             a.oper_code,
             a.oper_name,
             a.channel_id,
             a.order_month,
             a.xf_month,
             a.dgp_fees,
             a.return_cycle,
             b.return_ratio,
             a.dgp_fees * b.return_ratio,
             a.month_id
        from (select prov_name,
                     busi_type,
                     oper_code,
                     oper_name,
                     channel_id,
                     order_month,
                     xf_month,
                     dgp_fees,
                     PERIOD_DIFF(xf_month, order_month) return_cycle,
                     month_id
                from dmk_fsyx_bcxyhj
               where month_id = v_month) a
        join (select * from dim_business_model where month_id = v_month) b
          on a.busi_type = b.busi_type
         and a.prov_name = b.prov_name
         and a.return_cycle = b.return_cycle
         and a.order_month between b.start_time and b.end_time
        join (select * from dim_fsyx_oper_type where month_id = v_month) c
          on a.oper_code = c.oper_code
         and a.busi_type = c.busi_type
        join (select * from dim_fsyx_ch_type where month_id = v_month) d
          on a.prov_name = d.prov_name
         and a.channel_id = d.channel_id;
    elseIF @v_type=3 THEN
    delete from dmk_fsyx_zb_rpt where  month_id=v_month;
    insert into dmk_fsyx_zb_rpt
      (prov_name, zxy_fees, bcxy_fees, bcxy_dgpfees, sett_fees, month_id)
      select ab.prov_name,
             sum(ab.zxy_fees),
             sum(ab.bcxy_fees),
             sum(ab.bcxy_dgpfees),
             sum(ab.zxy_fees) + sum(ab.bcxy_fees) - sum(ab.bcxy_dgpfees),
             ab.month_id
        from (select prov_name,
                     sett_fees zxy_fees,
                     '0' bcxy_fees,
                     '0' bcxy_dgpfees,
                     month_id
                from dmk_fsyx_zxy
               where month_id = v_month
              union all
              select prov_name,
                     '0' zxy_fees,
                     sett_dgp_fees bcxy_fees,
                     '0' bcxy_dgpfees,
                     month_id
                from dmk_fsyx_bcxyhj
               where month_id = v_month
              union all
              select prov_name,
                     '0' zxy_fees,
                     '0' bcxy_fees,
                     sett_info_fees bcxy_dgpfees,
                     month_id
                from dmk_fsyx_bcxyyj
               where month_id = v_month) ab
       group by ab.prov_name, ab.month_id;
    end if;
     END

2.Dao层代码：

//调用存储过程
    	@Select({ "call ${DM_SCHEMA}.p_dmk_fsyx(#{v_month,mode=IN,jdbcType=VARCHAR},"
    			+ "#{v_type,mode=IN,jdbcType=VARCHAR},#{result,mode=OUT,jdbcType=INTEGER})"
    			 })
    	@Options(statementType=StatementType.CALLABLE)
    	void callProcedure(@Param("v_month") String v_month,
                @Param("v_type") String v_type,
                @Param("DM_SCHEMA") String DM_SCHEMA
                );

3.service层里调用dao层：

@Override
    	public void callReportProcedure(String settMonth, String type) throws Exception {
    		String DM_SCHEMA = propertyManager.get(SystemConfigKey.DM_SCHEMA_KEY);
            log.info("获取仓库数据名称为:[" + DM_SCHEMA + "]");
            divideProvDao.callProcedure(settMonth, type, DM_SCHEMA);
    	}