mysql 游标遍历存储过程

通过创建带游标的存储过程，实现遍历并处理数据的功能，其中使用到了while循环，需要注意的是：declare continue HANDLER for not found set done = true;  该语句定义的变量属于全局变量，只要有一条select语句，有一个select为空，该变量即自动设置为true。

CREATE PROCEDURE `StatisticDailyProblem`()
BEGIN  
    declare total int;  
		declare count int;
    declare entCode varchar(18);  
		declare dataCode varchar(20);  
		declare dataCodeName varchar(30);  
		declare dataValue decimal(18,4);  
		declare mediumValue decimal(18,4);  
		declare statType varchar(1);  
		declare statDate datetime;  
		declare uploadDate datetime;  
		declare stdApiCode varchar(20);  
		declare lastDataValue varchar(20);  
		declare lastStatDate datetime;  
		declare errorType varchar(1);  
    declare done int default false; 
    declare cur cursor for select t.entCode,t.dataCode,t.dataCodeName,convert(t.dataValue,decimal(18,4)) as dataValue,t.mediumValue,t.statType,t.statDate,t.uploadDate,t.stdApiCode from atm_upload_status t where t.statType='1';  
    declare continue HANDLER for not found set done = true;  
		
    set total = 0;  
    open cur;  
    fetch cur into entCode,dataCode,dataCodeName,dataValue,mediumValue,statType,statDate,uploadDate,stdApiCode;  
    while(not done) do  
				set total = total + 1;
        -- select entCode,dataCode,dataCodeName,dataValue,mediumValue,statType,statDate,uploadDate,stdApiCode;
				if dataValue=0 or (mediumValue>0 and (dataValue>=mediumValue*3 or dataValue<=mediumValue*0.2)) then
				    if dataValue=0 then set errorType=1;
						else set errorType=2;
						end if;
						set lastDataValue=null;set lastStatDate=null;
						select t.dataValue,t.statDate into lastDataValue,lastStatDate from atm_upload_status_history t where t.entCode=entCode and t.dataCode=dataCode and t.statType=statType and t.stdApiCode=stdApiCode and t.statDate<statDate order by t.statDate desc limit 1;
				    select count(1) into count from atm_dailyproblem t where t.EntCode=entCode and t.DataCode=dataCode and t.StatType=statType and t.stdApiCode=stdApiCode;
						if count=0 then 
								insert into atm_dailyproblem (EntCode,DataCode,StatType,DataCodeName,StatDate,DataValue,LastStatDate,LastDataValue,MediumValue,ErrorType,stdApiCode) values (entCode,dataCode,statType,dataCodeName,statDate,dataValue,lastStatDate,lastDataValue,mediumValue,errorType,stdApiCode);
						else 
								update atm_dailyproblem t set t.StatDate=statDate,t.DataValue=dataValue,t.LastStatDate=lastStatDate,t.LastDataValue=lastDataValue,t.MediumValue=mediumValue,t.ErrorType=errorType,t.stdApiCode=stdApiCode where t.EntCode=entCode and t.DataCode=dataCode and t.StatType=statType and t.stdApiCode=stdApiCode;
						end if;
				end if;
				
				set done=false;
        fetch cur into entCode,dataCode,dataCodeName,dataValue,mediumValue,statType,statDate,uploadDate,stdApiCode;  
    end while;  
      
    close cur;  
    select total;  
END