DELIMITER $$
USE `data`$$
DROP PROCEDURE IF EXISTS `fn_json_GetTeachnew`$$
CREATE DEFINER=`root`@`%` PROCEDURE `fn_json_GetTeachnew`(
p_Aid VARCHAR(64),
p_Cursor INT,
p_Newtype VARCHAR(64))
BEGIN
DECLARE ssql VARCHAR(100);
DECLARE pageSize INT;
DECLARE limitCount INT;
DECLARE tempCount INT;
DECLARE p_Next_Cursor VARCHAR(64);
SET pageSize=20;
SET p_Next_Cursor='';
SET limitCount=p_Cursor*pageSize;
DROP TEMPORARY TABLE IF EXISTS tmp_table_GetTeachnew;
CREATE TEMPORARY TABLE tmp_table_GetTeachnew
SELECT `NewId`,`Title`,`Content`,`PicUrl`,`NewTypeTitle` AS NewType,`PublishDate`,`IsHot`
FROM `tb_fdt_new_teachnew` t INNER JOIN `tb_fdt_new_newtype` n
WHERE t.`AgentId`=p_Aid
AND t.`NewType`=n.`NewType`
AND n.`NewTypeTitle`=p_Newtype
ORDER BY PublishDate DESC;
SET tempCount=(SELECT COUNT(*) FROM tmp_table_GetTeachnew); -- 统计看有多少新闻,然后看看你这次获取了之后,下次还能不能获取,如果下次能获取,返回数字,不能获取返回空
IF(tempCount>p_Cursor*pageSize+pageSize) THEN
SET p_Next_Cursor=p_Cursor+1;
END IF;
SET ssql="SELECT g.*,? as nextCursor FROM tmp_table_GetTeachnew g LIMIT ?,?";
SET @query=p_Next_Cursor;
SET @fvar=limitCount;
SET @svar=pageSize;
SET @SQUERY=ssql;
PREPARE STMT FROM @SQUERY;
EXECUTE STMT USING @query,@fvar,@svar;
END$$
DELIMITER ;