MySql 多表分页存储过程
MySql 多表分页存储过程
CREATE PROCEDURE `sp_pager`(
p_table_name VARCHAR(500),/*表名*/
p_fields VARCHAR(1024),/*查询字段*/
p_page_now INT,/*当前页*/
p_page_size INT,/*记录数*/
p_where_string VARCHAR(1024),/*WHERE条件*/
p_order_string VARCHAR(200))
BEGIN
DECLARE m_begin_row INT;
DECLARE m_limit_string VARCHAR(100) DEFAULT '';
DECLARE colum VARCHAR(1024);
DECLARE conditions VARCHAR(1024);
DECLARE _err int DEFAULT 0;/*是否出错*/
DECLARE error_message varchar(1024);
DECLARE p_out_rows INT;/*输出记录总数*/
DECLARE error_code varchar(50);/*错误代码*/
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION,SQLWARNING,NOT FOUND set _err=1;
/*要查询的列名*/
IF (p_fields <> '')
THEN
SET colum = p_fields;
ELSE
SET colum = CONCAT('*');
END IF;
/*查询条件*/
IF (p_where_string <> '') THEN
SET conditions = CONCAT(' WHERE ',p_where_string);
ELSE
SET conditions = CONCAT('WHERE 1=1');
END IF;
IF (p_page_now=0 && p_page_size=0) THEN
SET m_limit_string = '';
ELSE
SET m_begin_row = (p_page_now - 1) * p_page_size;
SET m_limit_string = CONCAT(' LIMIT ', m_begin_row, ', ', p_page_size);
END IF;
/*总记录数*/
SET @rescount = CONCAT('SELECT COUNT(*) INTO @ROWS_TOTAL FROM ', p_table_name, ' ', conditions);
/*分页查询结果集*/
SET @result = CONCAT('SELECT ',colum,' FROM ',p_table_name,' ',conditions,' ',p_order_string,m_limit_string);
PREPARE rescount FROM @rescount;
EXECUTE rescount;
DEALLOCATE PREPARE rescount;
SET p_out_rows = @ROWS_TOTAL;
PREPARE result FROM @result;
EXECUTE result;
DEALLOCATE PREPARE result;
IF (_err=1) THEN
SET error_code = 'false';
ELSE
SET error_code = 'true';
END IF;
select p_out_rows as rows,error_code as error_code;
END