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 '分頁存儲過程'
www.2cto.com
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)//
