DELIMITER $$;DROP PROCEDURE IF EXISTS `mytest`.`MyPage`$$CREATE DEFINER=`root`@`localhost` PROCEDURE `MyPage`( tableName varchar(100), /*表名*/ fieldsName VARCHAR(100), /*显示字段*/ pageIndex int, /*页索引*/ pageSize int, /*每页大小*/ sortName VARCHAR(500), /*排序字段*/ strWhere varchar(500) /*条件*/ ) BEGIN DECLARE fieldlist VARCHAR(200); if fieldsName=''||fieldsName=null THEN set fieldlist='*'; else set fieldlist=fieldsName; end if; if strWhere=''||strWhere=null then if sortName=''||sortName=null then set @strSQL=concat('select ',fieldlist,' from ' , tableName,' limit ',(pageindex-1)*pageSize,',',pageSize); else set @strSQL=concat('select ',fieldlist,' from ' , tableName,' order by ',sortName,' limit ',(pageindex-1)*pageSize,',',pageSize); end if; else if sortName=''||sortName=null then set @strSQL=concat('select ',fieldlist,' from ' , tableName,' where ',strWhere,' limit ',(pageindex-1)*pageSize,',',pageSize); else set @strSQL=concat('select ',fieldlist,' from ' , tableName,' where ',strWhere,' order by ',sortName,' limit ',(pageindex-1)*pageSize,',',pageSize); end if; end if; PREPARE stmt1 FROM @strSQL; EXECUTE stmt1; DEALLOCATE PREPARE stmt1; END$$DELIMITER ;$$
tableName varchar(100), /*表名*/
fieldsName VARCHAR(100), /*显示字段*/
pageIndex int, /*页索引*/
pageSize int, /*每页大小*/
sortName VARCHAR(500), /*排序字段*/
strWhere varchar(500) /*条件*/
)
BEGIN
DECLARE fieldlist VARCHAR(200);
if fieldsName=''||fieldsName=null THEN
set fieldlist='*';
else
set fieldlist=fieldsName;
end if;
if strWhere=''||strWhere=null then
if sortName=''||sortName=null then
set @strSQL=concat('select ',fieldlist,' from ' , tableName,' limit ',(pageindex-1)*pageSize,',',pageSize);
else
set @strSQL=concat('select ',fieldlist,' from ' , tableName,' order by ',sortName,' limit ',(pageindex-1)*pageSize,',',pageSize);
end if;
else
if sortName=''||sortName=null then
set @strSQL=concat('select ',fieldlist,' from ' , tableName,' where ',strWhere,' limit ',(pageindex-1)*pageSize,',',pageSize);
else
set @strSQL=concat('select ',fieldlist,' from ' , tableName,' where ',strWhere,' order by ',sortName,' limit ',(pageindex-1)*pageSize,',',pageSize);
end if;
end if;
PREPARE stmt1 FROM @strSQL;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;
END$$DELIMITER ;$$