DROP PROCEDURE IF EXISTS `proc_page`;
DELIMITER $$
CREATE PROCEDURE `proc_page`(in $qtable varchar(255),in $qcols varchar(255),in $qwheres varchar(255),in $okey varchar(50),IN $oby VARCHAR(4),in $pagesize int,in $pageno int)
BEGIN
/**
* Name: proc_page
* Description:
* 支持翻页和排序功能
* Support page and sort functions.
* Parameter:
* $qtable -- 表名/table name
* $qcols -- 查询列/The columns of the query
* $qwheres -- 查询条件/The query conditions, no condition input is null
* $okey -- 排序字段/The sort field of the query
* $oby -- 排序类型/The type of query sort in ascending or descending fixed value [asc|desc]
* $pagesize -- 每页大小/The number of records per page
* $pageno -- 页码/The current page number, starting page number 1
* Version: 1.00
* Author: zhen.zhang@neusoft.com
*/
declare istart int default 0;
declare isize INT DEFAULT 0;
##
if $pageno>0 then
set istart=($pageno-1)*$pagesize;
END IF;
##
set isize=$pagesize;
if isnull($okey) then
IF ISNULL($qwheres) THEN
##
SET @result=CONCAT(' select ',$qcols,' from ',$qtable,' limit ',istart,',',isize);
ELSE
##
SET @result=CONCAT(' select ',$qcols,' from ',$qtable,' where ',$qwheres,' limit ',istart,',',isize);
END IF;
else
if isnull($oby) then
set $oby = 'asc';
end if;
if isnull($qwheres) then
##
SET @result=CONCAT(' select ',$qcols,' from ',$qtable,' order by ',$okey,' ',$oby,' limit ',istart,',',isize);
else
##
SET @result=CONCAT(' select ',$qcols,' from ',$qtable,' where ',$qwheres,' order by ',$okey,' ',$oby,' limit ',istart,',',isize);
end if;
end if;
##
Prepare result_stmt from @result;
Execute result_stmt;
Deallocate prepare result_stmt;
END $$
DELIMITER ;
