mysql动态分页存储过程

DELIMITER $$

USE `shopsplatform`$$

DROP PROCEDURE IF EXISTS `P_viewPage`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `P_viewPage`(   
       IN $TableName VARCHAR(200),  
       IN  $FieldList VARCHAR(2000),  
       IN   $PrimaryKey VARCHAR(100),  
       IN   $WHERE VARCHAR(1000),  
       IN  $ORDER VARCHAR(1000),  
       IN  $SortType INT,  /*排序方式， asc  /desc*/
       IN  $RecorderCount INT,  
       IN  $PageSize INT,  
       IN  $PageIndex INT,  
OUT    $TotalCount INTEGER,  
OUT    $TotalPageCount INTEGER  
)
BEGIN  
   
 
IF !(($TableName IS NULL OR $TableName='') OR ($FieldList IS NULL OR $FieldList='') OR ($PrimaryKey IS NULL OR $PrimaryKey='') OR $SortType < 1 OR $SortType >3 OR $RecorderCount < 0 OR $PageSize < 0 OR $PageIndex < 0) THEN  
IF ($WHERE IS NULL OR $WHERE='') THEN  
  SET @new_where1 = ' ' ;  
  SET @new_where2 = ' WHERE ' ;  
ELSE  
  SET @new_where1 =CONCAT(' WHERE ',$WHERE);  
  SET @new_where2 =CONCAT(' WHERE ',$WHERE,' AND ');  
END IF;  
IF $ORDER='' OR $SortType = 1 OR $SortType = 2 THEN  
  IF $SortType = 1 THEN  
       SET @new_order =CONCAT(' ORDER BY ',$PrimaryKey,' ASC' );  
    END IF;  
  IF $SortType = 2 THEN  
       SET @new_order =CONCAT(' ORDER BY ',$PrimaryKey,' DESC');  
    END IF;  
ELSE  
  SET @new_order =CONCAT(' ORDER BY ',$ORDER);  
END IF;  
SET @SqlCount = CONCAT('SELECT COUNT(*) into @TotalCount FROM ',$TableName,@new_where1);  
SET @SqlCount1 = CONCAT('SELECT CEILING((COUNT(*)+0.0)/',$PageSize,') into @TotalPageCount FROM ',$TableName,@new_where1);  
IF $RecorderCount = 0 THEN  
     PREPARE stmt1 FROM @SqlCount;  
     EXECUTE stmt1;  
     SET $TotalCount=@TotalCount;  
     PREPARE stmt1 FROM @SqlCount1;  
     EXECUTE stmt1;  
     SET $TotalPageCount=@TotalPageCount;  
ELSE  
  SET $TotalCount = $RecorderCount;  
END IF;  
IF $PageIndex > CEILING(($TotalCount+0.0)/$PageSize) THEN  
  SET $PageIndex = CEILING(($TotalCount+0.0)/$PageSize);  
END IF;  
IF $PageIndex = 0 OR $PageIndex = 1 THEN  
   SET @SQL=CONCAT('SELECT ',$FieldList,' FROM ',$TableName,@new_where1,@new_order,' limit ',$PageSize);  
ELSE  
  IF $SortType = 1 THEN  
       SET @SQL=CONCAT('SELECT ',$FieldList,' FROM ',$TableName,@new_where2,$PrimaryKey,' > (SELECT max(',$PrimaryKey,') FROM (SELECT ',$PrimaryKey,' FROM ',$TableName,@new_where1,@new_order,' limit ',$PageSize*($PageIndex-1),' ) AS TMP) ',@new_order,' limit ',$PageSize);  
  END IF;  
  IF $SortType = 2 THEN  
       SET @SQL=CONCAT('SELECT ',$FieldList,' FROM ',$TableName,@new_where2,$PrimaryKey,' < (SELECT MIN(',$PrimaryKey,') FROM (SELECT ',$PrimaryKey,' FROM ',$TableName,@new_where1,@new_order,' limit ',$PageSize*($PageIndex-1),' ) AS TMP) ',@new_order,' limit ',$PageSize);  
    END IF;  
  IF $SortType = 3 THEN  
      IF INSTR($ORDER,',') > 0 THEN  
            SET @SQL=CONCAT('SELECT ',$FieldList,' FROM ',$TableName,@new_where2,$PrimaryKey,' NOT IN (SELECT ',$PrimaryKey,' FROM (SELECT ',$PrimaryKey,' FROM ',$TableName,@new_where1,@new_order,' limit ',$PageSize*($PageIndex-1),' ) a)',@new_order,' limit ',$PageSize);  
      ELSE  
            SET @new_order =CONCAT(' ORDER BY ',$PrimaryKey,' ASC' );  
            SET @SQL=CONCAT('SELECT ',$FieldList,' FROM ',$TableName,@new_where2,$PrimaryKey,' > (SELECT max(',$PrimaryKey,') FROM (SELECT ',$PrimaryKey,' FROM ',$TableName,@new_where1,@new_order,' limit ',$PageSize*($PageIndex-1),' ) AS TMP) ',@new_order,' limit ',$PageSize);  
      END IF;  
  END IF;  
END IF;  
PREPARE stmt2 FROM @SQL;  
EXECUTE stmt2;  
END IF;  
END $$
DELIMITER ;

DELIMITER $$USE `shopsplatform`$$DROP PROCEDURE IF EXISTS `P_viewPage`$$CREATE DEFINER=`root`@`localhost` PROCEDURE `P_viewPage`( IN $TableName VARCHAR(200), IN $FieldList VARCHAR(2000), IN $PrimaryKey VARCHAR(100), IN $WHERE VARCHAR(1000), IN $ORDER VARCHAR(1000),
 IN $SortType INT, /*排序方式， asc /desc*/ IN $RecorderCount INT, IN $PageSize INT, IN $PageIndex INT, OUT $TotalCount INTEGER, OUT $TotalPageCount INTEGER )BEGIN IF !(($TableName IS NULL OR $TableName='') OR ($FieldList IS NULL OR $FieldList='') OR ($PrimaryKey
 IS NULL OR $PrimaryKey='') OR $SortType < 1 OR $SortType >3 OR $RecorderCount < 0 OR $PageSize < 0 OR $PageIndex < 0) THEN IF ($WHERE IS NULL OR $WHERE='') THEN SET @new_where1 = ' ' ; SET @new_where2 = ' WHERE ' ; ELSE SET @new_where1 =CONCAT(' WHERE ',$WHERE);
 SET @new_where2 =CONCAT(' WHERE ',$WHERE,' AND '); END IF; IF $ORDER='' OR $SortType = 1 OR $SortType = 2 THEN IF $SortType = 1 THEN SET @new_order =CONCAT(' ORDER BY ',$PrimaryKey,' ASC' ); END IF; IF $SortType = 2 THEN SET @new_order =CONCAT(' ORDER BY ',$PrimaryKey,'
 DESC'); END IF; ELSE SET @new_order =CONCAT(' ORDER BY ',$ORDER); END IF; SET @SqlCount = CONCAT('SELECT COUNT(*) into @TotalCount FROM ',$TableName,@new_where1); SET @SqlCount1 = CONCAT('SELECT CEILING((COUNT(*)+0.0)/',$PageSize,') into @TotalPageCount FROM
 ',$TableName,@new_where1); IF $RecorderCount = 0 THEN PREPARE stmt1 FROM @SqlCount; EXECUTE stmt1; SET $TotalCount=@TotalCount; PREPARE stmt1 FROM @SqlCount1; EXECUTE stmt1; SET $TotalPageCount=@TotalPageCount; ELSE SET $TotalCount = $RecorderCount; END IF;
 IF $PageIndex > CEILING(($TotalCount+0.0)/$PageSize) THEN SET $PageIndex = CEILING(($TotalCount+0.0)/$PageSize); END IF; IF $PageIndex = 0 OR $PageIndex = 1 THEN SET @SQL=CONCAT('SELECT ',$FieldList,' FROM ',$TableName,@new_where1,@new_order,' limit ',$PageSize);
 ELSE IF $SortType = 1 THEN SET @SQL=CONCAT('SELECT ',$FieldList,' FROM ',$TableName,@new_where2,$PrimaryKey,' > (SELECT max(',$PrimaryKey,') FROM (SELECT ',$PrimaryKey,' FROM ',$TableName,@new_where1,@new_order,' limit ',$PageSize*($PageIndex-1),' ) AS TMP)
 ',@new_order,' limit ',$PageSize); END IF; IF $SortType = 2 THEN SET @SQL=CONCAT('SELECT ',$FieldList,' FROM ',$TableName,@new_where2,$PrimaryKey,' < (SELECT MIN(',$PrimaryKey,') FROM (SELECT ',$PrimaryKey,' FROM ',$TableName,@new_where1,@new_order,' limit
 ',$PageSize*($PageIndex-1),' ) AS TMP) ',@new_order,' limit ',$PageSize); END IF; IF $SortType = 3 THEN IF INSTR($ORDER,',') > 0 THEN SET @SQL=CONCAT('SELECT ',$FieldList,' FROM ',$TableName,@new_where2,$PrimaryKey,' NOT IN (SELECT ',$PrimaryKey,' FROM (SELECT
 ',$PrimaryKey,' FROM ',$TableName,@new_where1,@new_order,' limit ',$PageSize*($PageIndex-1),' ) a)',@new_order,' limit ',$PageSize); ELSE SET @new_order =CONCAT(' ORDER BY ',$PrimaryKey,' ASC' ); SET @SQL=CONCAT('SELECT ',$FieldList,' FROM ',$TableName,@new_where2,$PrimaryKey,'
 > (SELECT max(',$PrimaryKey,') FROM (SELECT ',$PrimaryKey,' FROM ',$TableName,@new_where1,@new_order,' limit ',$PageSize*($PageIndex-1),' ) AS TMP) ',@new_order,' limit ',$PageSize); END IF; END IF; END IF; PREPARE stmt2 FROM @SQL; EXECUTE stmt2; END IF; END
 $$DELIMITER ;