mysql分頁預存程序

標籤：

CREATE PROCEDURE CommonPageProc(

    IN    p_tablename        VARCHAR(1024),      
    IN    p_fields            VARCHAR(1024),      
    IN    p_pagesize            INT,              
    IN    p_pageindex            INT,              
    IN    p_orderfield        VARCHAR(128),      
    IN    p_sqlwhere        VARCHAR(1024),     
    OUT    p_totalrecord            INT    
   
)
    NOT DETERMINISTIC
    SQL SECURITY DEFINER
    COMMENT ‘分頁預存程序‘
   
BEGIN

    /*定義變數*/
    DECLARE m_begin_row INT DEFAULT 0;
    DECLARE m_limit_string CHAR(64);

    /*構造語句*/   
    SET m_begin_row = (p_pageindex - 1) * p_pagesize;
    SET m_limit_string = CONCAT(‘ LIMIT ‘, m_begin_row, ‘,‘, p_pagesize);
    if (p_sqlwhere<>‘‘ ) THEN
     set p_sqlwhere=concat(‘ where ‘,p_sqlwhere);
    end if;
    if p_orderfield<>‘‘ then
     set p_orderfield=concat(‘ order by ‘,p_orderfield);
     end if;
   
    SET @COUNT_STRING = CONCAT(‘SELECT COUNT(*) INTO @ROWS_TOTAL FROM ‘, p_tablename, ‘ ‘, p_sqlwhere);
    SET @MAIN_STRING = CONCAT(‘SELECT ‘, p_fields, ‘ FROM ‘, p_tablename, ‘ ‘, p_sqlwhere, ‘ ‘, p_orderfield, m_limit_string);

    /*預先處理*/
    PREPARE count_stmt FROM @COUNT_STRING;
    EXECUTE count_stmt;
    DEALLOCATE PREPARE count_stmt;
    SET p_totalrecord = @ROWS_TOTAL;

    PREPARE main_stmt FROM @MAIN_STRING;
    EXECUTE main_stmt;
    DEALLOCATE PREPARE main_stmt;
   
END

mysql分頁預存程序