預存程序的分頁源碼

 

drop procedure if exists pr_pager;
create procedure pr_pager(

    in     p_table_name        varchar(1024),        /*表名*/
    in     p_fields            varchar(1024),        /*查詢欄位*/
    in     p_page_size            int,                /*每頁記錄數*/
    in     p_page_now            int,                /*當前頁*/
    in     p_order_string        varchar(128),        /*排序條件(包含order關鍵字,可為空白)*/
    in     p_where_string        varchar(1024),        /*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;

     prepareE main_stmt from @main_string;
    execute main_stmt;
     deallocate  prepare main_stmt;
   
end;

調用
mysql> call pr_pager("t","var",3,3, "","",@result);
mysql> call pr_pager("t","var",3,2, "","",@result);