mysql paging stored procedure (1)

2010-09-25  来源:本站原创  分类:Database  人气:211 

DROP PROCEDURE IF EXISTS `pro_pager`;   

CREATE DEFINER = `root`@`%` PROCEDURE `pro_pager`(
in p_pageNo int,        /* The current page  */
in p_perPageCnt int,    /* The number of records per page  */
in p_sql VARCHAR(2000), /* Query SQL statement  */
out v_totalRowsCnt int, /* Record the total number of  */
out v_totalPageCnt int  /* Note the number of pages  */ )
BEGIN
  /* When an incoming query pages is null or  <1 , Fu  p_pageNo=1 */
  IF p_pageNo IS NULL OR p_pageNo < 1 THEN
    SET p_pageNo = 1;
  END IF;    

  SET @rowsCnt = 0;
    SET @pagesCnt = 0;
  SET @sqlCnt = CONCAT('select count(1) into @rowsCnt from (',p_sql,') as t');  --  Statistics on the total number of records  sql   

  /* Statistics on the total number of records-pretreatment  */
  PREPARE s_cnt from @sqlCnt;
  EXECUTE s_cnt;
  DEALLOCATE PREPARE s_cnt;
  SET v_totalRowsCnt = @rowsCnt;   

  -- SET @pagesCnt = floor((@rowsCnt + p_perPageCnt - 1) / p_perPageCnt);  --  Calculate the total number of pages
  SET @pagesCnt = ceil(@rowsCnt / p_perPageCnt); --  Calculate the total number of pages
  /* When an incoming query pages  > The total number of pages, enabling  p_pageNo= The total number of pages   */
  IF p_pageNo > @pagesCnt THEN
    SET p_pageNo = @pagesCnt;
  END IF;   

  SET v_totalPageCnt = @pagesCnt;
  SET @limitStart = (p_pageNo - 1) * p_perPageCnt;  --  Query record starting line
  SET @limitEnd = p_perPageCnt;  --  Query record end line
  SET @sqlQry = CONCAT(p_sql, ' limit ', @limitStart, ',', @limitEnd);  --  Query record sets  sql   

  /* Query record set — pretreatment  */
  PREPARE record from @sqlQry;
  EXECUTE record;
  DEALLOCATE PREPARE record;
END ;
相关文章
  • mysql paging stored procedure (1) 2010-09-25

    DROP PROCEDURE IF EXISTS `pro_pager`; CREATE DEFINER = `root`@`%` PROCEDURE `pro_pager`( in p_pageNo int, /* The current page */ in p_perPageCnt int, /* The number of records per page */ in p_sql VARCHAR(2000), /* Query SQL statement */ out v_totalRo

  • mysql paging stored procedure (2) 2010-09-25

    import java.sql.CallableStatement; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import org.junit.Test; public class Test2 { @Test public void test() throws SQLException { Connecti

  • mysql paging stored procedure (a) 2010-09-25

    DROP PROCEDURE IF EXISTS `pro_pager`; CREATE DEFINER = `root`@`%` PROCEDURE `pro_pager`( in p_pageNo int, /* The current page */ in p_perPageCnt int, /* Number of records per page */ in p_sql VARCHAR(2000), /* Query SQL statement */ out v_totalRowsCn

  • mysql paging stored procedure (b) 2010-09-25

    import java.sql.CallableStatement; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import org.junit.Test; public class Test2 { @Test public void test() throws SQLException { Connecti

  • Paging stored procedure (transfer) 2010-04-11

    /***************************************************** MySQL Paging stored procedure Wu sword 2009-07-02 *****************************************************/ DROP PROCEDURE IF EXISTS pr_pager; CREATE PROCEDURE pr_pager( IN p_table_name VARCHAR(1024

  • row number paging stored procedure 2010-09-02

    USE [GoodsSystem] GO /****** Object : StoredProcedure [dbo].[proc_table_paging] Date script : 08/30/2010 16:03:48 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO /* Use ROW_NUMBER() OVER Examples of page WITH tb_temp AS (SELECT *,ROW_NUMBER(

  • A wonderful near-perfect paging stored procedure 2011-07-16

    CREATE procedure main_table_pwqzc (@pagesize int, @pageindex int, @docount bit, @this_id int) as if(@docount=1) begin select count(id) from luntan where [email protected]_id end else begin declare @PageLowerBound int declare @PageUpperBound int set @Page

  • Universal SQL paging stored procedure 2011-07-21

    Note: The stored procedure must be the primary sort key, otherwise the page may not be successful Stored Procedure 1: set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go -- Parameters ------------------------------------------------------------- /**//* @st

  • Comments ------- MySql merge stored procedure cursor 2010-09-21

    book content: id name appraisal 1 max A 2 max B 3 kelongmao C 4 zero D 5 kelongmao E 6 max F 7 zero G 8 kelongmao H 9 zero I book2 content: id name appraisal 1 max ABF 2 zero DG 3 kelongmao CEH create table book ( id int primary key, name varchar (20

  • sql indexing, and optimized the sql statement, sql paging stored procedure (transfer) 2011-03-24

    When using the clustered index or non-clustered index The following table summarizes when to use clustered index or non-clustered index (very important). Action Description Using the clustered index Use of non-clustered index Column sort is often gro

  • [Transfer] SQL paging stored procedure (not in model. Dichotomy to achieve) and the resulting problems 2011-09-03

    Turn: http://www.itphome.cn/shujukuyingyong/mssql/2010-01-27/149.html Dichotomy is the conventional page-mode paging mode improvements, but if sort field has duplicate values (ie not primarykey) is likely to appear on the first page and second page o

  • MySQL triggers. Stored procedure. Custom functions. View instance 2010-03-22

    1, the syntax: CREATE TRIGGER <trigger name> - the trigger must have a name, up to 64 characters, may later be attached to a separator. It, and MySQL in naming other objects basic resemble. (BEFORE | AFTER) - set the trigger with the implementation

  • Using the ROW_NUMBER () paging stored procedure 2010-03-26

    Make a new project today, when used in page, from the previous projects done before the stored procedure to find a page, can be paged by passing the table name, and use the ROW_NUMBER () function, so only above applies MSSQL2005, first written down i

  • Little meaning paging stored procedure 2010-05-31

    CREATE procedure main_table_pwqzc (@pagesize int, @pageindex int, @docount bit, @this_id) as if(@docount=1) begin select count(id) from luntan where [email protected]_id end else begin declare @indextable table(id int identity(1,1),nid int) declare @Page

  • Some time ago to see a tutorial sirloin saw his brother with a paging stored procedure 2010-05-31

    In the SQL stored procedure in the establishment of the following Create PROCEDURE [dbo].[proc_ShowPage] @tblName varchar(255), -- Table name @strGetFields varchar(1000) = '*', -- Need to return the column, the default * @strOrder varchar(255)='', --

  • Universal paging stored procedure 2010-06-03

    Transfer from: http://51cndo.blog.51cto.com/491222/102320 Yesterday was a stored procedure encountered trouble, and then search for the answer to the search, "Universal stored procedure", but only see some incomplete code, multi-reference, the I

  • Use the ROW_NUMBER () paging stored procedure 2010-06-04

    2010-03-26 Articles Category: Database to do a new project today, when used in page, from the previous projects done before the stored procedure to find a page, can be paged by passing the table name, and use the ROW_NUMBER () function, so the above

  • MySQL database stored procedure 2010-06-05

    We all know that MySQL stored procedures from MySQL 5.0 began adding new features. Stored procedure and an advantage in practical application than disadvantages. But the most important thing is the efficiency and SQL code packages, especially SQL cod

  • Paging stored procedure 2010-06-09

    In SQL Server2005 added a new function: row_number (), returns the result set partition expert serial number. So I will now use this function to be a common paging method, and before that we used three methods often compared to its advantages: first,

  • mysql database stored procedure to view information 2011-07-26

    Query the database stored procedures Method One: select `name` from mysql.proc where db = 'your_db_name' and `type` = 'PROCEDURE' Method Two: show procedure status; View the creation of a stored procedure or function code show create procedure proc_n