这个存储过程有一个地方,存在错误,就是每次获取到一共多少页都是一样的,需要修改一处地方,加一个where条件,问怎么修改?
CREATE PROCEDURE pages
(
tableName varchar(200),#表名
columnName varchar(1000), #列名列表
whereStr varchar(1000), #查询条件
orderBy varchar(200),
currentPage int, #当前第几页
pageSize int, #每页多少条记录
out pageCount int #一共多少页
)
begin
declare p int default 0;
declare begin_row int default 0;
declare limit_string varchar(200);
#获取数据库总记录
set @countSql = concat('select count(*) into @count from ',tableName);
PREPARE countsql FROM @countSql;
EXECUTE countsql;
DEALLOCATE PREPARE countsql;
#获取一共多少页
set p = FLOOR(@count / pageSize);
if @count % pageSize != 0 then
set p = p+1;
end if;
set pageCount = p; set begin_row = (currentPage - 1)*pageSize;
set limit_string = CONCAT(' limit ',begin_row,',',pageSize);
set @mainSql = CONCAT('select ',columnName,' from ',tableName,' ',whereStr,' ',orderBy,' ',limit_string);
PREPARE mainSql FROM @mainSql;
EXECUTE mainSql;
DEALLOCATE PREPARE mainSql;end
CREATE PROCEDURE pages
(
tableName varchar(200),#表名
columnName varchar(1000), #列名列表
whereStr varchar(1000), #查询条件
orderBy varchar(200),
currentPage int, #当前第几页
pageSize int, #每页多少条记录
out pageCount int #一共多少页
)
begin
declare p int default 0;
declare begin_row int default 0;
declare limit_string varchar(200);
#获取数据库总记录
set @countSql = concat('select count(*) into @count from ',tableName);
PREPARE countsql FROM @countSql;
EXECUTE countsql;
DEALLOCATE PREPARE countsql;
#获取一共多少页
set p = FLOOR(@count / pageSize);
if @count % pageSize != 0 then
set p = p+1;
end if;
set pageCount = p; set begin_row = (currentPage - 1)*pageSize;
set limit_string = CONCAT(' limit ',begin_row,',',pageSize);
set @mainSql = CONCAT('select ',columnName,' from ',tableName,' ',whereStr,' ',orderBy,' ',limit_string);
PREPARE mainSql FROM @mainSql;
EXECUTE mainSql;
DEALLOCATE PREPARE mainSql;end
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货