参照存储过程如下 : 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
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
想说的这样,不在程序中判断就需要在数据库中判断 ,拼接条件是不能避免的!
MYSQL数据库容量理论上没有限制,取决于数据库服务器机器的操作系统对文件大小的限制决定的,与MYSQL本身没有直接关系!