Mysql分页存储过程_MySQL-mysql教程

DROP PROCEDURE IF EXISTS pr_pager;CREATE PROCEDURE pr_pager(    IN    p_table_name        VARCHAR(1024),            IN    p_fields            VARCHAR(1024),            IN    p_page_size            INT,                    IN    p_page_now            INT,                    IN    p_order_string        VARCHAR(128),            IN    p_where_string        VARCHAR(1024),             OUT    p_page_count            INT                        )    NOT DETERMINISTIC     SQL SECURITY DEFINER     COMMENT &＃39;分页存储过程&＃39;    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(&＃39; LIMIT &＃39;, m_begin_row, &＃39;, &＃39;, p_page_size);        SET @COUNT_STRING = CONCAT(&＃39;SELECT COUNT(*) INTO @ROWS_TOTAL FROM &＃39;, p_table_name, &＃39; &＃39;, p_where_string);    SET @MAIN_STRING = CONCAT(&＃39;SELECT &＃39;, p_fields, &＃39; FROM &＃39;, p_table_name, &＃39; &＃39;, p_where_string, &＃39; &＃39;, p_order_string,m_limit_string);        PREPARE count_stmt FROM @COUNT_STRING;    EXECUTE count_stmt;    DEALLOCATE PREPARE count_stmt;    SET p_page_count = CEILING((@ROWS_TOTAL*1.0)/p_page_size);    PREPARE main_stmt FROM @MAIN_STRING;    EXECUTE main_stmt;    DEALLOCATE PREPARE main_stmt;    END;--测试call pr_pager("person","id",4,2,"order by id desc","",@page_count);select @page_count as page_count;

    IN    p_table_name         VARCHAR(1024),     --表名     IN    p_fields             VARCHAR(1024),     --输出的字段名     IN    p_page_size          INT,               --页容量    IN    p_page_now           INT,               --当前页     IN    p_order_string       VARCHAR(128),      --排序语句段 ，如：order by id desc      IN    p_where_string       VARCHAR(1024),     --where语句段    OUT   page_count           INT                --输出的总页数