CREATE PROCEDURE CommonPageProc(
IN p_tablename VARCHAR(1024),
IN p_fields VARCHAR(1024),
IN p_pagesize INT,
IN p_pageindex INT,
IN p_orderfield VARCHAR(128),
IN p_sqlwhere VARCHAR(1024),
OUT p_totalrecord INT
)
NOT DETERMINISTIC
SQL SECURITY DEFINER
COMMENT '分页存储过程'
BEGIN
/*定义变量*/
DECLARE m_begin_row INT DEFAULT 0;
DECLARE m_limit_string CHAR(64);
/*构造语句*/
SET m_begin_row = (p_pageindex - 1) * p_pagesize;
SET m_limit_string = CONCAT(' LIMIT ', m_begin_row, ',', p_pagesize);
if (p_sqlwhere<>'' ) THEN
set p_sqlwhere=concat(' where ',p_sqlwhere);
end if;
if p_orderfield<>'' then
set p_orderfield=concat(' order by ',p_orderfield);
end if;
SET @COUNT_STRING = CONCAT('SELECT COUNT(*) INTO @ROWS_TOTAL FROM ', p_tablename, ' ', p_sqlwhere);
SET @MAIN_STRING = CONCAT('SELECT ', p_fields, ' FROM ', p_tablename, ' ', p_sqlwhere, ' ', p_orderfield, m_limit_string);
/*预处理*/
PREPARE count_stmt FROM @COUNT_STRING;
EXECUTE count_stmt;
DEALLOCATE PREPARE count_stmt;
SET p_totalrecord = @ROWS_TOTAL;
PREPARE main_stmt FROM @MAIN_STRING;
EXECUTE main_stmt;
DEALLOCATE PREPARE main_stmt;
END