基于Mysql的通用分页存储过程

3ewsd

CREATE PROCEDURE `P_viewPage`(　　$TableName VARCHAR(200),
　　$FieldList VARCHAR(2000),
　　$PrimaryKey VARCHAR(100),
　　$Where VARCHAR(1000),
　　$Order VARCHAR(1000),
　　$SortType INT,
　　$RecorderCount INT,
　　$PageSize INT,
　　$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 ;