1、mysql教程之存储>> 最近用mysql+asp.来写网站，既然mysql已经支持存储过程了，那么像分页这么常用的东西，当然要用存储过程啦 不过在网上找了一些，发现都有一个特点就是不能传出总记录数，干脆自己研究吧。终于，算是搞出来了，效率可能不是很好，但是我也觉得不错了。贴代码吧直接：也算是对自己学习mysql的一个记录。 CREATE PROCEDURE p_pageList ( m_pageNo int , m_perPageCnt int , m_column varchar(1000) , m_table varchar(1000) , m_conditio
2、n 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(
3、9;select ',m_column,' from ',m_table); IF m_condition IS NOT NULL AND m_condition <> '' THEN SET sql = CONCAT(sql,' _condition); SET sqlCnt = CONCAT(sqlCnt,' _condition); END IF; IF m_orderBy IS NOT NULL AND m_orderBy <> '' THEN SET sql = CON
4、CAT(sql,' order by ',m_orderBy); END IF; SET sql = CONCAT(sql, ' limit ', limitStart, ',', limitEnd); PREPARE s_t from sqlCnt; EXECUTE s_t; DEALLOCATE PREPARE s_t; SET m_totalPageCnt = pageCnt; PREPARE record from sql; EXECUTE record; DEALLOCATE PREPARE record;
