MySql 通用分页存储过程_pagehelper对oracle视图进行分页查询无法返回数据-优快云博客

本文链接：https://blog.youkuaiyun.com/wuwei_longlong/article/details/6044803

本文介绍了一个MySQL存储过程Prc_Paging，用于实现灵活的分页查询。该过程支持单表、视图及多表组合查询，可通过输入参数定义查询字段、页大小、当前页数等，以获取指定范围的数据记录。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

CREATE DEFINER = 'bluesoleil' @'localhost' PROCEDURE ` Prc_Paging ` (
	IN TableName TEXT,
	IN Fields TEXT,
	IN PageSize INTEGER ( 11 ),
	IN CurrentPage INTEGER ( 11 ),
	IN WhereStr TEXT,
	IN GroupCondition VARCHAR ( 255 ),
	IN OrderCondition VARCHAR ( 255 ),
	OUT Out_Count INTEGER ( 11 ) 
	) NOT DETERMINISTIC CONTAINS SQL SQL SECURITY DEFINER COMMENT '' BEGIN
	DECLARE	m_begin_row INT DEFAULT 0;
	DECLARE	m_limit_string CHAR ( 64 ) DEFAULT "";
	
	SET m_begin_row = ( CurrentPage - 1 ) * PageSize;
	
	SET m_limit_string = CONCAT ( ' LIMIT ', m_begin_row, ', ', PageSize );
	
	SET @theCount = 0;
	
	SET @COUNT_STRING = CONCAT ( 'SELECT COUNT(*) INTO @theCount FROM ', TableName, ' ', WhereStr );
	
	SET @MAIN_STRING = CONCAT ( 'SELECT ', Fields, ' FROM ', TableName, ' ', WhereStr, ' ', GroupCondition, ' order by ', OrderCondition, ' ', m_limit_string );
	PREPARE count_stmt 
	FROM @COUNT_STRING;
	EXECUTE count_stmt;
	DEALLOCATE PREPARE count_stmt;
	IF	@theCount IS NULL 
        THEN			
			SET Out_Count = 0;
		ELSE 
			SET Out_Count = @theCount;
		
	END IF;
	PREPARE main_stmt 
	FROM
		@MAIN_STRING;
	EXECUTE main_stmt;
	DEALLOCATE PREPARE main_stmt;
END;

适用于单个表,视图,和多表组合查询.