mysql 存储过程写分页程序

/*分页程序*/

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)//