Mysql分页存储过程

来源：互联网发布：单片机手机软件编辑：程序博客网时间：2024/05/04 01:36

</pre><p><pre name="code" class="sql">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 '分页存储过程'    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_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                --输出的总页数

0 0