框架学习

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