bitsCN.com
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)// bitsCN.com

下载本文