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 --输出的总页数