Mysql tutorial stored procedure _ MySQL

Source: Internet
Author: User
In the mysql tutorial, the stored procedure bitsCN.com is not found on the Internet, and all of them have a feature-that is, the total number of records cannot be transferred out. let's study it by yourself. Finally, it was achieved, and the efficiency may not be very good, but I also think it is good. Paste the code directly: It is also a record for you to learn 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; -- Total number of records
SET @ limitStart = (m_pageNo-1) * m_perPageCnt;
SET @ limitEnd = m_perPageCnt;
SET @ sqlCnt = CONCAT ('SELECT count (1) into @ pageCnt from ', m_table); -- this statement is critical to obtain the total value.
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
BitsCN.com

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.