mysql 游标CURSOR

 

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;