参照存储过程如下 :
CREATE
PROCEDURE ps_yddl_get_gyjx_jgm_d(IN ps_jgm char(8) CHARACTER SET utf8, IN ps_ywbh varchar(20) CHARACTER SET utf8, IN ps_gybh varchar(350) CHARACTER SET utf8, IN ps_ksrq varchar(10) CHARACTER SET utf8, IN ps_jsrq varchar(10) CHARACTER SET utf8, IN ps_start int(11), IN ps_limit int(11), IN ps_pxfs int(11))
COMMENT '柜员天绩效情况查询'
BEGIN
DECLARE ps_sqlwhere VARCHAR(200) default ' ';
DECLARE ps_order VARCHAR(50);
SET ps_sqlwhere = concat(' where rq between "', ps_ksrq, '" and "', ps_jsrq,'"');
CALL splitString(ps_gybh, "-");
CASE ps_pxfs
WHEN '3' THEN
SET ps_order = ' order by rq ASC ,dgytjid ASC';
WHEN '2' THEN
SET ps_order = ' order by ywl DESC ,dgytjid ASC ';
ELSE
SET ps_order = ' order by pjfwsj ASC ,dgytjid ASC ';
END CASE;
IF (ps_jgm != 'all') THEN
SET ps_sqlwhere = concat(ps_sqlwhere, ' and jgm = ', ps_jgm);
END IF;
IF (ps_ywbh != 'all') THEN
SET ps_sqlwhere = concat(ps_sqlwhere, ' and ywbh = ', ps_ywbh);
END IF;
IF (ps_gybh != 'all') THEN
SET ps_sqlwhere = concat(ps_sqlwhere, ' and gybh in (select * from tmp_split) ');
END IF;
SET @ps_sqltotal = concat('select count(*) into @total from jx_dgy_tj ', ps_sqlwhere);
SET @ps_sqltext = concat('select dgytjid, jgm, gybh, gyxm, ywbh, ywl, zgpf, zdpf, pjpf, zdfwsj, zcfwsj, pjfwsj, fwsjpm, ywlpm, rq, xq, wdmc,@total as total from jx_dgy_tj ', ps_sqlwhere, ps_order, ' limit ', ps_start, ',', ps_limit);
PREPARE stmt FROM @ps_sqltotal;
execute stmt;
PREPARE stmt2 FROM @ps_sqltext;
execute stmt2;
END