存储过程的分页源码

 

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