CREATE PROCEDURE `prc_page_result`(in currpage int,
in columns varchar(4000),
in tablename varchar(4000),
in sCondition varchar(21845),
in order_field varchar(100),
in asc_field int,
in primary_field varchar(100),
in pagesize int,
out recordCount int)
begin
declare sTemp varchar(21845);
declare sSql varchar(21845);
declare sOrder varchar(1000);
if asc_field = 1 then
set sOrder = concat(' order by ', order_field, ' desc ');
set sTemp = '<(select min';
else
set sOrder = concat(' order by ', order_field, ' asc ');
set sTemp = '>(select max';
end if;
SET @pageCnt = 1; -- 总记录数
if sCondition <> '' then
SET @sqlCnt=CONCAT('select count(1) into @pageCnt from ',tablename,' where ',sCondition);
ELSE
SET @sqlCnt=CONCAT('select count(1) into @pageCnt from ',tablename);
end IF;
PREPARE s_cnt from @sqlCnt;
EXECUTE s_cnt;
-- DEALLOCATE PREPARE s_cnt;
SET recordCount = @pageCnt;
if currpage<1 THEN
SET currpage=1;
end IF;
#SET @pageIndex=CEILING(@pageCnt / pagesize);
#SET currpage=@pageIndex;
if sCondition <> '' then
set sSql = concat('select ', columns, ' from ', tablename);
set sSql = concat(sSql, ' where ', sCondition);
set sSql = concat(sSql, ' order by ', order_field);
if asc_field=1 then
set sSql = concat(sSql, ' desc ');
else
set sSql = concat(sSql, ' asc ');
end if;
set sSql = concat(sSql, ' limit ?,? ');
else
set sSql = concat('select ', columns, ' from ', tablename);
#set sSql = concat(sSql, ' where ', sCondition);
set sSql = concat(sSql, ' order by ', order_field);
if asc_field=1 then
set sSql = concat(sSql, ' desc ');
else
set sSql = concat(sSql, ' asc ');
end if;
set sSql = concat(sSql, ' limit ?,? ');
end if;
set @iPageSize = pagesize;
set @iPageIndex=(currpage-1)*pagesize;
set @sQuery = sSql;
prepare stmt from @sQuery;
execute stmt using @iPageIndex,@iPageSize;
end
转载请注明原文地址: https://ju.6miu.com/read-35924.html
