FETCH cursor_works INTO num,provinceIDs,cityIDs,SourceID;
定义的变量值必须与 游标中的字段不同,一一对应
DECLARE cursor_works CURSOR FOR SELECT worksSum,provinceID,cityID,SourceType FROM worksTemp;
CREATE PROCEDURE pro_province_report (IN startDate VARCHAR(20),IN endDate VARCHAR(20),IN SourceType INT) /* 功能:根据时间,来源SourceType 统计 每个省份的 注册用户 投稿数 作品点击量 投票量 参与人数 pro_province_report(); */ BEGIN DECLARE num INT DEFAULT 0; DECLARE provinceIDs INT DEFAULT 0; DECLARE cityIDs INT DEFAULT 0; DECLARE SourceID INT DEFAULT 0; #用户注册 临时表 DROP TEMPORARY TABLE IF EXISTS memberUserTemp; CREATE TEMPORARY TABLE memberUserTemp SELECT COUNT(1) accountSum,provinceID,province,cityID,SourceType FROM MEMBERUSER WHERE provinceID>0 AND cityID>0 AND DATE(addDate)=DATE(now()) GROUP BY provinceID,cityID,SourceType; #投稿数 临时表 DROP TEMPORARY TABLE IF EXISTS worksTemp; CREATE TEMPORARY TABLE worksTemp SELECT COUNT(1) worksSum,provinceID,province,cityID,SourceType FROM WORKS WHERE provinceID>0 AND cityID>0 AND DATE(addDate)=DATE(now()) GROUP BY provinceID,cityID,SourceType; #用户注册信息 BEGIN DECLARE Done INT DEFAULT FALSE; #声明游标 DECLARE cursor_memberUser CURSOR FOR SELECT accountSum,provinceID,cityID,SourceType FROM memberUserTemp WHERE cityID>0; #将结束标志绑定到游标 DECLARE CONTINUE HANDLER FOR NOT FOUND SET Done = TRUE; #打开游标 OPEN cursor_memberUser; read_loop: LOOP #逐个取出当前记录accountSum,provinceID,SourceType字段的值, FETCH cursor_memberUser INTO num,provinceIDs,cityIDs,SourceID; #遍历数据 IF Done THEN LEAVE read_loop; END IF; #正常逻辑 判断 统计表中 当天 相同来源 相同省份 相同城市 是否存在数据,存在则更新,不存在则插入 SELECT COUNT(1) into @count FROM REPORT WHERE SourceType=SourceID AND ProvinceID=provinceIDs AND CityID=cityIDs AND DATE(addDate)=DATE(now()); IF (@count>0) THEN UPDATE REPORT SET MemberNum=num WHERE SourceType=SourceID AND ProvinceID=provinceIDs AND CityID=cityIDs AND DATE(addDate)=DATE(now()); ELSE INSERT INTO REPORT(SourceType,ProvinceID,CityID,MemberNum,WorkNum,PointNum,VoteNum,JoinPeopleNum,VotePeopleNum,addDate)VALUES(SourceID,provinceIDs,cityIDs,num,0,0,0,0,0,NOW()); END IF; END LOOP; #关闭游标 CLOSE cursor_memberUser; END; #插入投稿数 BEGIN DECLARE Done2 INT DEFAULT FALSE; #声明游标 DECLARE cursor_works CURSOR FOR SELECT worksSum,provinceID,cityID,SourceType FROM worksTemp; #将结束标志绑定到游标 DECLARE CONTINUE HANDLER FOR NOT FOUND SET Done2 = TRUE; #打开游标 OPEN cursor_works; read_loop: LOOP #逐个取出当前记录accountSum,provinceID,SourceType字段的值, FETCH cursor_works INTO num,provinceIDs,cityIDs,SourceID; #遍历数据 IF Done2 THEN LEAVE read_loop; END IF; #正常逻辑 判断 统计表中 当天 相同来源 相同省份 相同城市 是否存在数据,存在则更新,不存在则插入 SELECT COUNT(1) into @count FROM REPORT WHERE SourceType=SourceID AND ProvinceID=provinceIDs AND CityID=cityIDs AND DATE(addDate)=DATE(now()); IF (@count>0) THEN UPDATE REPORT SET WorkNum=num WHERE SourceType=SourceID AND ProvinceID=provinceIDs AND CityID=cityIDs AND DATE(addDate)=DATE(now()); ELSE INSERT INTO REPORT(SourceType,ProvinceID,CityID,MemberNum,WorkNum,PointNum,VoteNum,JoinPeopleNum,VotePeopleNum,addDate)VALUES(SourceID,provinceIDs,cityIDs,0,num,0,0,0,0,NOW()); END IF; END LOOP; #关闭游标 CLOSE cursor_works; END; BEGIN DECLARE sql_str VARCHAR(1000); #组合条件 DECLARE dateStr VARCHAR(200); #条件 DECLARE sourceStr VARCHAR(200); #来源条件 IF(startDate!=''&&endDate!='') THEN SET dateStr=CONCAT( ' AND DATE(addDate)>=DATE("',startDate,'") AND DATE(addDate)<=DATE("',endDate,'")' ); ELSE SET dateStr=" AND 1=1 "; END IF; IF(SourceType>0) THEN SET sourceStr=CONCAT(' AND SourceType="',SourceType,'" '); ELSE SET sourceStr=" AND 1=1 "; END IF; SET sql_str =CONCAT(' SELECT p.province,tab.* from( SELECT COUNT(1) counts, ProvinceID,SUM(MemberNum) MemberNum ,SUM(WorkNum) WorkNum,SUM(PointNum) PointNum,SUM(VoteNum) VoteNum, SUM(JoinPeopleNum) JoinPeopleNum,SUM(VotePeopleNum) VotePeopleNum FROM REPORT where 1=1 ',dateStr,sourceStr,' GROUP BY ProvinceID ) tab INNER JOIN PROVINCE p ON tab.ProvinceID=p.id'); set @sql_str=sql_str; #将连成成的字符串赋值给一个变量(可以之前没有定义,但要以@开头) prepare stmt from @sql_str; #预处理需要执行的动态SQL,其中stmt是一个变量 EXECUTE stmt; #执行SQL语句 deallocate prepare stmt; #释放掉预处理段 END; END;