MySqlページング・ストレージ・プロシージャ

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;