create or replace procedure PageList(tbName IN VARCHAR(255),tbFieldsIN VARCHAR(1000), orderField IN VARCHAR(255),orderType IN INT,strWhere IN VARCHAR(1000),pageSize IN INT,pageIndex IN INT,pageRecord OUT INT)
is
BEGIN
/*定义变量*/
DECLARE m_begin_row INT DEFAULT 0;
DECLARE m_limit_string CHAR(64);
DECLARE m_order_string CHAR(128);
DECLARE orderStr CHAR(64);
DECLARE whereStr VARCHAR(1000);
/*构造语句*/
if (orderType=1) then
set orderStr = 'asc';
else
set orderStr = 'desc';
end if;
if (strWhere<>'') then
set whereStr = CONCAT(' where ',strWhere,' ');
else
set whereStr = ' ';
end if;
SET m_begin_row = (pageIndex - 1) * pageSize;
SET m_limit_string = CONCAT(' LIMIT ', m_begin_row, ', ', pageSize);
SET m_order_string = CONCAT(' order by ',orderField,' ',orderStr);
SET @COUNT_STRING = CONCAT('SELECT COUNT(*) INTO @ROWS_TOTAL FROM ', tbName, ' ', whereStr);
SET @MAIN_STRING = CONCAT('SELECT ', tbFields, ' FROM ', tbName, ' ', whereStr, ' ', m_order_string, m_limit_string);
/*预处理*/
PREPARE count_stmt FROM @COUNT_STRING;
EXECUTE count_stmt;
DEALLOCATE PREPARE count_stmt;
SET pageRecord = @ROWS_TOTAL;
PREPARE main_stmt FROM @MAIN_STRING;
EXECUTE main_stmt;
DEALLOCATE PREPARE main_stmt;
END
