create procedure pr_pager(
in p_table_name varchar(100), /*表名*/
in p_fields varchar(100), /*查询字段*/
in p_page_size int, /*每页记录数*/
in p_page_now int, /*当前页*/
in p_order_string varchar(100), /*排序条件,包含order关键字,可为空*/
in p_where_string varchar(100), /*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;