存储过程的输入输出参数在 sql串 中的拼写

现有存储过程包含两个输入参数和四个输出参数

实体类：

private String userId;//用户id
private String userName;//用户姓名
private String officeId;//部门id
private String officeName;//部门名称
private Integer createWkstNum;//创建工单数
private Integer handleNum;//待处理工单数
private Integer claimNum;//待认领工单数
private Integer alreadyHandleNum;//已办理工单数
private Integer overdueNum;//已超期工单数
private String workNo;//工号

mapper文件

<select id="findWkstStateNumByUserIds" parameterType="com.thinkgem.jeesite.modules.sys.entity.UserVo" statementType="CALLABLE" resultType="com.thinkgem.jeesite.modules.sys.entity.UserVo"> 

<![CDATA[  
  {call p_wkststate_num_by_userids(
  #{userId,mode=IN,jdbcType=VARCHAR},
  #{officeId,mode=IN,jdbcType=VARCHAR},
 
  #{overdueNum,mode=OUT,jdbcType=INTEGER},
 
  #{handleNum,mode=OUT,jdbcType=INTEGER},
  #{claimNum,mode=OUT,jdbcType=INTEGER},
  #{alreadyHandleNum,mode=OUT,jdbcType=INTEGER}
  )} 
 
]]> 

 

根据业务需求需要动态的根据时间查询员工相关工单各个状态工单的数量

所以需要增加两个输出参数

in begin_date varchar(64),in end_date varchar(64),in user_id varchar(64),in office_id varchar(64),out overdue_num int,out handle_num int,out claim_num int,
out already_handle_num int

mapper与之对应也增加两个输出参数

<select id="findWkstStateNumByUserIds" parameterType="com.thinkgem.jeesite.modules.sys.entity.UserVo" statementType="CALLABLE" resultType="com.thinkgem.jeesite.modules.sys.entity.UserVo"> 

<![CDATA[  
  {call p_wkststate_num_by_userids(
  #{beginDate,mode=IN,jdbcType=VARCHAR},
  #{endDate,mode=IN,jdbcType=VARCHAR},
 
  #{userId,mode=IN,jdbcType=VARCHAR},
  #{officeId,mode=IN,jdbcType=VARCHAR},
 
  #{overdueNum,mode=OUT,jdbcType=INTEGER},
 
  #{handleNum,mode=OUT,jdbcType=INTEGER},
  #{claimNum,mode=OUT,jdbcType=INTEGER},
  #{alreadyHandleNum,mode=OUT,jdbcType=INTEGER}
  )} 
 
]]> 

存储过程变为：

为了方便，双斜杠为注释符号

 

1，输入参数用‘,参数,’标记

2，输出参数用@参数标记

3，需要注意的是在拼接sql串的情况下，java代码传输入的字符串参数需要强制加单引号

否则传到sql的时候就没有单引号了，会报错

如： uv.setEndDate("'"+uVo.getEndDate()+"'");
uv.setUserId("'"+uv.getUserId()+"'");
uv.setOfficeId("'"+uv.getOfficeId()+"'");

否则sql解析时则不是字符串

4，mapper中没有日期类型的参数

用TO_DAYS(日期）解析字符串日期

 

 

BEGIN

set @exesqloverdue =CONCAT('SELECT count(1) into@overdue_num  FROM crm_worksheet a    
LEFT JOIN crm_worksheet_flow wf ON wf.id = a.worksheet_flow_id
where now()>wf.handle_term_date and a.worksheet_state=3 and wf.handle_per= ',user_id,' ');
IF begin_date is not null and begin_date !=''  THEN
set @exesqloverdue =CONCAT(@exesqloverdue ,' AND TO_DAYS(a.update_date) >= TO_DAYS(',begin_date,')');     //CONCAT 追加sql串
END IF;


IF end_date is not null and end_date !='' THEN
set @exesqloverdue =CONCAT(@exesqloverdue ,' AND TO_DAYS(a.update_date) <= TO_DAYS(',end_date,')');
END IF;
prepare overdue from @exesqloverdue;//编译字符串
  execute overdue;//运行字符串
SET overdue_num = @overdue_num  ;//为返回参数赋值


set  @exesqlhandle =CONCAT('SELECT count(1) into @handle_num FROM crm_worksheet a
LEFT JOIN crm_worksheet_flow wf ON wf.id = a.worksheet_flow_id
where a.worksheet_state=3 and wf.handle_per=',user_id,''  );
IF begin_date is not null and begin_date !=''  THEN
set @exesqlhandle =CONCAT(@exesqlhandle ,' AND TO_DAYS(a.update_date) >= TO_DAYS(',begin_date,')');
END IF;


IF end_date is not null and end_date !='' THEN
set @exesqlhandle =CONCAT(@exesqlhandle ,' AND TO_DAYS(a.update_date) <= TO_DAYS(',end_date,')');
END IF;


prepare handle from @exesqlhandle;
  execute handle;
SET  handle_num = @handle_num  ;




set  @exesqlclaimNum =CONCAT('SELECT count(1) into @claim_num FROM crm_worksheet a
LEFT JOIN crm_worksheet_flow wf ON wf.id = a.worksheet_flow_id
where a.worksheet_state=2 and wf.handle_org=',office_id,'');
IF begin_date is not null and begin_date !=''  THEN
set @exesqlclaimNum =CONCAT(@exesqlclaimNum ,' AND TO_DAYS(a.update_date) >= TO_DAYS(',begin_date,')');
END IF;


IF end_date is not null and end_date !='' THEN
set @exesqlclaimNum =CONCAT(@exesqlclaimNum ,' AND TO_DAYS(a.update_date) <= TO_DAYS(',end_date,')');
END IF;


prepare claimNum from @exesqlclaimNum;
  execute claimNum;
SET claim_num = @claim_num  ;




set @exesqlalready =CONCAT('select count(1) into @already_handle_num from crm_worksheet_flow a 
where a.flow_cmd=1 
and a.handle_per is not null
and a.handle_org is not null
and a.handle_per !='' and a.handle_org !=''
and a.handle_per=',user_id,'') ;  


IF begin_date is not null and begin_date !=''  THEN
set @exesqlalready =CONCAT(@exesqlalready ,' AND TO_DAYS(a.update_date) >= TO_DAYS(',begin_date,')');
END IF;


IF end_date is not null and end_date !='' THEN
set @exesqlalready =CONCAT(@exesqlalready ,' AND TO_DAYS(a.update_date) <= TO_DAYS(',end_date,')');
END IF;


prepare already from @exesqlalready;
  execute already;
SET already_handle_num = @already_handle_num  ;



END

 

 

 

参数：

in begin_date varchar(64),in end_date varchar(64),in user_id varchar(64),in office_id varchar(64),out overdue_num int,out handle_num int,out claim_num int,
out already_handle_num int

 

注意：

最近又对这个存储过程编写的时候发现一个bug:

sql串的拼写时时间的比较 一定要用  <=  而不是  <   否则查询结果有误，真是奇了怪

环境：windows下的5.6.21