mysql分页存储过程
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
郑重声明：本站内容如果来自互联网及其他传播媒体，其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享，并不代表本站赞同其观点和对其真实性负责，也不构成任何其他建议。
