MySql 分页存储过程

旧城等待， 2024-02-17 14:43 142阅读 0赞

DELIMITER $$ #修改分隔符为 $$
    DROP PROCEDURE IF EXISTS sp_MvcCommonDataSource$$ #分隔符
    CREATE PROCEDURE sp_MvcCommonDataSource (
    	#输入参数
    	_fields VARCHAR(2000), #要查询的字段，用逗号(,)分隔
    	_tables TEXT,  #要查询的表
    	_where VARCHAR(2000),   #查询条件
    	_orderby VARCHAR(200),  #排序规则
    	_pageindex INT,  #查询页码
    	_pageSize INT,   #每页记录数
    	_sumfields VARCHAR(200),#求和字段
    	#输出参数
    	OUT _totalcount INT,  #总记录数
    	OUT _pagecount INT,    #总页数
    	OUT _sumResult VARCHAR(2000)#求和结果
    )
    BEGIN
    	#140529-xxj-分页存储过程
    	#计算起始行号
    	SET @startRow = _pageSize * (_pageIndex - 1);
    	SET @pageSize = _pageSize;
    	SET @rowindex = 0; #行号
    
    	#合并字符串
    	SET @strsql = CONCAT(
    		#'select sql_calc_found_rows  @rowindex:=@rowindex+1 as rownumber,' #记录行号
    		'select sql_calc_found_rows '
    		,_fields
    		,' from '
    		,_tables
    		,CASE IFNULL(_where, '') WHEN '' THEN '' ELSE CONCAT(' where ', _where) END
    		,CASE IFNULL(_orderby, '') WHEN '' THEN '' ELSE CONCAT(' order by ', _orderby) END
    	  ,' limit ' 
    		,@startRow
    		,',' 
    		,@pageSize
    	);
    
    	PREPARE strsql FROM @strsql;#定义预处理语句 
    	EXECUTE strsql;							#执行预处理语句 
    	DEALLOCATE PREPARE strsql;	#删除定义 
    	#通过 sql_calc_found_rows 记录没有使用 limit 语句的记录，使用 found_rows() 获取行数
    	SET _totalcount = FOUND_ROWS();
    
    	#计算总页数
    	IF (_totalcount <= _pageSize) THEN
    		SET _pagecount = 1;
    	ELSE IF (_totalcount % _pageSize > 0) THEN
    		SET _pagecount = _totalcount / _pageSize + 1;
    	ELSE
    		SET _pagecount = _totalcount / _pageSize;
    	END IF;
    	END IF;
    
    	#计算求和字段
    	IF (IFNULL(_sumfields, '') <> '') THEN
    		#序列sum结果
    		SET @sumCols = CONCAT (
    			'CONCAT_WS(\',\','
    			,'SUM('
    			,REPLACE(_sumfields,',','),SUM(')
    			,'))');
    		#拼接字符串
    		SET @sumsql = CONCAT(
    			'select '
    			,@sumCols
    			,' INTO @sumResult from '
    			,_tables
    			,CASE IFNULL(_where, '') WHEN '' THEN '' ELSE CONCAT(' where ', _where) END
    			,';'
    		);
    		#select @sumsql;
    		PREPARE sumsql FROM @sumsql;#定义预处理语句 
    		EXECUTE sumsql;	
    		SET _sumResult = @sumResult;						#执行预处理语句 
    		DEALLOCATE PREPARE sumsql;	#删除定义 
    
    	END IF;
    
    END$$
    DELIMITER ; #修改分隔符为分号(;)
    
    
    ##################################################
    # 测试存储过程
    #select order_no,order_date,order_type from `order`;
    
    CALL sp_MvcCommonDataSource(
    'order_no,order_date,order_type'#查询字段
    ,'`order`'#表名
    ,'1=1'#条件
    ,'order_no asc'#排序
    ,2 #页码
    ,3 #每页记录数
    ,'order_no,order_no'#求和字段
    ,@totalcount #输出总记录数
    ,@pagecount #输出用页数
    ,@sumResult #求和结果
    );
    SELECT @totalcount,@pagecount,@sumResult;