/*分页程序*/
delimiter //
drop procedure if exists sp_page;
create procedure sp_page(
p_table_name varchar(1024),
p_fields varchar(1024),
p_page_size int,
p_curr_page int,
p_order_string varchar(256),
p_where_string varchar(1024),
out p_total_rows int
)
not deterministic
sql security definer
comment '分页存储过程'
begin
/*变量的申明*/
declare v_start_row int default 0;
declare v_limit_string varchar(256);
/*给变量赋值*/
if p_curr_page<1 then
set p_curr_page = 1;
end if;
set @rows_total = 0;
set v_start_row = (p_curr_page-1)*p_page_size;
set v_limit_string = concat(' limit ',v_start_row,',',p_page_size);
set @total_string = concat('select count(*) into @rows_total from ',p_table_name,' ',p_where_string);
set @query_string = concat('select ',p_fields,' from ',p_table_name,' ',p_where_string,' ',
p_order_string,' ',v_limit_string);
select @total_string;
select @query_string;
/*预处理*/
prepare stmt_count from @total_string;
execute stmt_count;
deallocate prepare stmt_count;
set p_total_rows= @rows_total;
prepare stmt_query from @query_string;
execute stmt_query;
deallocate prepare stmt_query;
end;//
调用存储过程
参数含义
表的名字 要查询的字段 每页显示的记录数 当前的页码 总记录数
call sp_page('t_score','*',10,1,'','', @total_rows)//