mysql 教程 存储过程

rmrfmysql 2019-04-01

不过在网上找了一些,发现都有一个特点――就是不能传出总记录数,干脆自己研究吧。终于,算是搞出来了,效率可能不是很好,但是我也觉得不错了。贴代码吧直接:也算是对自己学习mysql的一个记录。
  

代码如下:

CREATE PROCEDURE p_pageList 
  ( 
  m_pageNo int , 
  m_perPageCnt int , 
  m_column varchar(1000) , 
  m_table varchar(1000) , 
  m_condition varchar(1000), 
  m_orderBy varchar(200) , 
  out m_totalPageCnt int 
  ) 
  BEGIN 
  SET @pageCnt = 1; -- 总记录数 
  SET @limitStart = (m_pageNo - 1)*m_perPageCnt; 
  SET @limitEnd = m_perPageCnt; 
  SET @sqlCnt = CONCAT('select count(1) into @pageCnt from ',m_table); -- 这条语句很关键,用来得到总数值 
  SET @sql = CONCAT('select ',m_column,' from ',m_table); 
  IF m_condition IS NOT NULL AND m_condition <> '' THEN 
  SET @sql = CONCAT(@sql,' where ',m_condition); 
  SET @sqlCnt = CONCAT(@sqlCnt,' where ',m_condition); 
  END IF; 
  IF m_orderBy IS NOT NULL AND m_orderBy <> '' THEN 
  SET @sql = CONCAT(@sql,' order by ',m_orderBy); 
  END IF; 
  SET @sql = CONCAT(@sql, ' limit ', @limitStart, ',', @limitEnd); 
  PREPARE s_cnt from @sqlCnt; 
  EXECUTE s_cnt; 
  DEALLOCATE PREPARE s_cnt; 
  SET m_totalPageCnt = @pageCnt; 
  PREPARE record from @sql; 
  EXECUTE record; 
  DEALLOCATE PREPARE record; 
  END

相关推荐