MySQL 存储过程中分页

2014-11-20 

　　MySQL数据库中，自定义存储过程查询表中的数据，带有分页功能。具体实例如下代码：

 1 DROP PROCEDURE IF EXISTS `sampledb`.`proc_GetPagedDataSet`;
 2 
 3 CREATE DEFINER=`root`@`%` PROCEDURE `proc_GetPagedDataSet`(
 4     IN tableName VARCHAR (20), /*表名
 5     IN pageIndex INT,  /*当前页*/
 6     IN pageSize INT,  /*每页记录数*/
 7     OUT pageCount INT,/*总记录分页数*/
 8     out totalRecordCount int /*总记录数*/)
 9 BEGIN
10    /*获取表中的记录数*/
11    set @recordCount=0;
12    set @sql=‘‘;
13    set @sql=CONCAT(‘select count(*) into @recordCount from ‘,tableName);

14    prepare stmt from @sql; /*预处理 自定义sql字符串*/
15    execute stmt; /*执行自定义sql语句*/
16    deallocate prepare stmt; /*释放预处理资源*/
17    
18    set totalRecordCount = @recordCount; /*总记录数*/
19    
20    /*计算返回多少页*/
21    set @tmp=1;
22    set @tmp=@recordCount mod pageSize; /*取余数*/
23    if @tmp=0 then
24      set pageCount=@recordCount div pageSize;
25    else
26      set pageCount=@recordCount div pageSize + 1;
27    end if;
28 
29    /*分页显示数据*/
30    set @sql=CONCAT(‘select * from ‘,tableName,‘ limit ‘,(pageIndex-1)*pageSize,‘,‘,pageSize);

31    prepare stmt from @sql; /*预处理 自定义sql字符串*/
32    execute stmt;/*执行自定义sql语句*/
33    deallocate prepare stmt; /*释放预处理资源*/
34 END