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 ;