56,687
社区成员
发帖
与我相关
我的任务
分享
DELIMITER $$
/*DROP PROCEDURE IF EXISTS `automachine`.`PRO_PAGER`*/
CREATE DEFINER=`root`@`localhost` PROCEDURE `automachine`.`PRO_PAGER`(
IN p_table_name VARCHAR(1000),
IN p_fields VARCHAR(1000),
IN p_page_size INT,
IN p_page_now INT,
IN p_order_string VARCHAR(128),
IN p_where_string VARCHAR(1000),
OUT p_out_rows INT
)
BEGIN
DECLARE m_begin_row INT DEFAULT 0;
DECLARE m_limit_string CHAR(64);
SET m_begin_row = (p_page_now - 1) * p_page_size;
SET m_limit_string = CONCAT(' LIMIT ', m_begin_row, ', ', p_page_size);
SET @COUNT_STRING = CONCAT('SELECT COUNT(*) INTO @ROWS_TOTAL FROM ', p_table_name, ' ', p_where_string);
SET @MAIN_STRING = CONCAT('SELECT ', p_fields, ' FROM ', p_table_name, ' ', p_where_string, ' ', p_order_string, m_limit_string);
PREPARE count_stmt FROM @COUNT_STRING;
EXECUTE count_stmt;
DEALLOCATE PREPARE count_stmt;
SET p_out_rows = @ROWS_TOTAL;
PREPARE main_stmt FROM @MAIN_STRING;
EXECUTE main_stmt;
DEALLOCATE PREPARE main_stmt;
END $$
DELIMITER ;
SET @p_page_now=1;
SET @p_page_size=20;
SET @m_limit_string=CONCAT(' LIMIT ', 1, ', ', 20);
SET @p_where_string='WHERE ID > 0';
SET @p_fields='*';
SET @COUNT_STRING = CONCAT('SELECT COUNT(*) INTO @ROWS_TOTAL FROM ', 'record', ' ', 'where id>0');
SET @MAIN_STRING = CONCAT('SELECT ', '*', ' FROM ', 'record', ' ', 'where id>0', ' ', 'order by id ', @m_limit_string);
CALL `automachine`.`PRO_PAGER`('record',@p_fields,20,5,'order by id ', @p_where_string ,@ROWS_TOTAL);
SELECT @ROWS_TOTAL;