通用分页存储过程,mysql通用分页存储过程

MySql分页存储过程
MySql测试版本：5.0.41-community-nt
/*****************************************************
MySQL分页存储过程
吴剑 2009-07-02
*****************************************************/
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), /*排序条件(包含ORDER关键字,可为空)*/
IN p_where_string VARCHAR(1024), /*WHERE条件(包含WHERE关键字,可为空)*/
OUT p_out_rows 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_out_rows = @ROWS_TOTAL;
PREPARE main_stmt FROM @MAIN_STRING;
EXECUTE main_stmt;
DEALLOCATE PREPARE main_stmt;
END;
第2个例子
use guestbook;
delimiter $$
drop procedure if exists prc_page_result $$
create procedure prc_page_result (
in currpage int,
in columns varchar(500),
in tablename varchar(500),
in sCondition varchar(500),
in order_field varchar(100),
in asc_field int,
in primary_field varchar(100),
in pagesize int
)
begin
declare sTemp varchar(1000);
declare sSql varchar(4000);
declare sOrder varchar(1000);
if asc_field = 1 then
set sOrder = concat(' order by ', order_field, ' desc ');
set sTemp = '<(select min';
else
set sOrder = concat(' order by ', order_field, ' asc ');
set sTemp = '>(select max';
end if;
if currpage = 1 then
if sCondition <> '' then
set sSql = concat('select ', columns, ' from ', tablename, ' where ');
set sSql = concat(sSql, sCondition, sOrder, ' limit ?');
else
set sSql = concat('select ', columns, ' from ', tablename, sOrder, ' limit ?');
end if;
else
if sCondition <> '' then
set sSql = concat('select ', columns, ' from ', tablename);
set sSql = concat(sSql, ' where ', sCondition, ' and ', primary_field, sTemp);
set sSql = concat(sSql, '(', primary_field, ')', ' from (select ');
set sSql = concat(sSql, ' ', primary_field, ' from ', tablename, sOrder);
set sSql = concat(sSql, ' limit ', (currpage-1)*pagesize, ') as tabtemp)', sOrder);
set sSql = concat(sSql, ' limit ?');
else
set sSql = concat('select ', columns, ' from ', tablename);
set sSql = concat(sSql, ' where ', primary_field, sTemp);
set sSql = concat(sSql, '(', primary_field, ')', ' from (select ');
set sSql = concat(sSql, ' ', primary_field, ' from ', tablename, sOrder);
set sSql = concat(sSql, ' limit ', (currpage-1)*pagesize, ') as tabtemp)', sOrder);
set sSql = concat(sSql, ' limit ?');
end if;
end if;
set @iPageSize = pagesize;
set @sQuery = sSql;
prepare stmt from @sQuery;
execute stmt using @iPageSize;
end;
$$
delimiter;
//调用
call prc_page_result(1, "字段列表", "表名", "条件", "排序字段", 1, "id", 25);
参数说明:
第几页,显示的字段(如name,id),表名,Where后面的条件,排序的字段,1表示降序,id主键,25每页显示条数

延伸阅读