1>通用分页存储过程代码及调用
/*****************************************************MySQL分页存储过程吴剑 2009-07-02 *****************************************************/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 DETERMINISTICSQL SECURITY DEFINERCOMMENT '分页存储过程' 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; PREPARE main_stmt FROM @MAIN_STRING; EXECUTE main_stmt; DEALLOCATE PREPARE main_stmt; END;1取记录调用：
call pr_pager('表名', '*', 25, 1, '', '', @count_rows);call pr_pager('user', '*', 15, 2, '', 'where id>3', @count_rows);call pr_pager('user', '*', 15, 1, 'group by password order by id desc', '', @count_rows);2调用1后再取条数调用：
select @count_rows; select @MAIN_STRING //select sqlselect @COUNT_STRING //seelct count sql
支持多表级联,分组：call pr_pager('job j left join enter_job ej on j.job_no=ej.job_no','j.*,ej.*','25','1','group by ej.put_away_user order by ej.put_away_user desc','where j.job_table="enter"',@p_out_rows);
?
?
?