MySql 分页存储过程

  1. DELIMITER $$ #修改分隔符为 $$  
  2. DROP PROCEDURE IF EXISTS sp_MvcCommonDataSource$$ #分隔符  
  3. CREATE PROCEDURE sp_MvcCommonDataSource (  
  4.     #输入参数  
  5.     _fields VARCHAR(2000), #要查询的字段,用逗号(,)分隔  
  6.     _tables TEXT,  #要查询的表  
  7.     _where VARCHAR(2000),   #查询条件  
  8.     _orderby VARCHAR(200),  #排序规则  
  9.     _pageindex INT,  #查询页码  
  10.     _pageSize INT,   #每页记录数  
  11.     _sumfields VARCHAR(200),#求和字段  
  12.     #输出参数  
  13.     OUT _totalcount INT,  #总记录数  
  14.     OUT _pagecount INT,    #总页数  
  15.     OUT _sumResult VARCHAR(2000)#求和结果  
  16. )  
  17. BEGIN  
  18.     #140529-xxj-分页存储过程  
  19.     #计算起始行号  
  20.     SET @startRow = _pageSize * (_pageIndex – 1);  
  21.     SET @pageSize = _pageSize;  
  22.     SET @rowindex = 0; #行号  
  23.   
  24.     #合并字符串  
  25.     SET @strsql = CONCAT(  
  26.         #‘select sql_calc_found_rows  @rowindex:=@rowindex+1 as rownumber,’ #记录行号  
  27.         ‘select sql_calc_found_rows ‘  
  28.         ,_fields  
  29.         ,‘ from ‘  
  30.         ,_tables  
  31.         ,CASE IFNULL(_where, WHEN  THEN  ELSE CONCAT(‘ where ‘, _where) END  
  32.         ,CASE IFNULL(_orderby, WHEN  THEN  ELSE CONCAT(‘ order by ‘, _orderby) END  
  33.       ,‘ limit ‘   
  34.         ,@startRow  
  35.         ,‘,’   
  36.         ,@pageSize  
  37.     );  
  38.   
  39.     PREPARE strsql FROM @strsql;#定义预处理语句   
  40.     EXECUTE strsql;                         #执行预处理语句   
  41.     DEALLOCATE PREPARE strsql;  #删除定义   
  42.     #通过 sql_calc_found_rows 记录没有使用 limit 语句的记录,使用 found_rows() 获取行数  
  43.     SET _totalcount = FOUND_ROWS();  
  44.   
  45.     #计算总页数  
  46.     IF (_totalcount <= _pageSize) THEN  
  47.         SET _pagecount = 1;  
  48.     ELSE IF (_totalcount % _pageSize > 0) THEN  
  49.         SET _pagecount = _totalcount / _pageSize + 1;  
  50.     ELSE  
  51.         SET _pagecount = _totalcount / _pageSize;  
  52.     END IF;  
  53.     END IF;  
  54.   
  55.     #计算求和字段  
  56.     IF (IFNULL(_sumfields, ) <> THEN  
  57.         #序列sum结果  
  58.         SET @sumCols = CONCAT (  
  59.             ‘CONCAT_WS(\’,\’,’  
  60.             ,‘SUM(‘  
  61.             ,REPLACE(_sumfields,‘,’,‘),SUM(‘)  
  62.             ,‘))’);  
  63.         #拼接字符串  
  64.         SET @sumsql = CONCAT(  
  65.             ‘select ‘  
  66.             ,@sumCols  
  67.             ,‘ INTO @sumResult from ‘  
  68.             ,_tables  
  69.             ,CASE IFNULL(_where, WHEN  THEN  ELSE CONCAT(‘ where ‘, _where) END  
  70.             ,‘;’  
  71.         );  
  72.         #select @sumsql;  
  73.         PREPARE sumsql FROM @sumsql;#定义预处理语句   
  74.         EXECUTE sumsql;   
  75.         SET _sumResult = @sumResult;                        #执行预处理语句   
  76.         DEALLOCATE PREPARE sumsql;  #删除定义   
  77.   
  78.     END IF;  
  79.   
  80. END$$  
  81. DELIMITER ; #修改分隔符为分号(;)  
  82.   
  83.   
  84. ##################################################  
  85. # 测试存储过程  
  86. #select order_no,order_date,order_type from `order`;  
  87.   
  88. CALL sp_MvcCommonDataSource(  
  89. ‘order_no,order_date,order_type’#查询字段  
  90. ,‘`order`’#表名  
  91. ,‘1=1’#条件  
  92. ,‘order_no asc’#排序  
  93. ,2 #页码  
  94. ,3 #每页记录数  
  95. ,‘order_no,order_no’#求和字段  
  96. ,@totalcount #输出总记录数  
  97. ,@pagecount #输出用页数  
  98. ,@sumResult #求和结果  
  99. );  
  100. SELECT @totalcount,@pagecount,@sumResult;  

标签