游标中嵌套有其他查询时要注意的问题
有如下存储过程：
BEGIN DECLARE assetId VARCHAR(16);DECLARE loadAverage1 FLOAT(10,2) DEFAULT 0;DECLARE loadAverage15 FLOAT(10,2) DEFAULT 0;DECLARE loadAverageCount INT; DECLARE loadResult_Tmp VARCHAR(8192) DEFAULT ''; DECLARE LoadAverageCursorDone INT DEFAULT 0; DECLARE LoadAverageCursor CURSOR FOR SELECT Asset_Id, Load_Average_1, Load_Average_15, Load_Average_Count from Load_Average_Info_Tmp; DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET LoadAverageCursorDone = 1; OPEN LoadAverageCursor; LoadAverageCursorLoop:LOOP FETCH LoadAverageCursor INTO assetId, loadAverage1, loadAverage15, loadAverageCount; IF LoadAverageCursorDone = 1 THEN LEAVE LoadAverageCursorLoop; END IF; SELECT Check_Result INTO loadResult_Tmp FROM Stat_CPU_All_Info_Tmp WHERE Asset_Id = assetId; /* ... */ END LOOP; CLOSE LoadAverageCursor;END;
?定义了一个游标用来遍历Load_Average_Info_Tmp表，每取得其中的一条数据根据取得的assetId查询Stat_CPU_All_Info_Tmp表。
?
此时遇到一个问题，Load_Average_Info_Tmp表没有遍历完提前退出了循环。
?
问题在游标里面的那条select语句：
SELECT Check_Result INTO loadResult_Tmp FROM Stat_CPU_All_Info_Tmp WHERE Asset_Id = assetId;
当游标中的这条SELECT语句查询结果为空时，会抛出一个'02000'状态而使得LoadAverageCursorDone=1，从而使得循环结束。
?
解决办法：
1.修改引起问题的SELECT语句，使其查询结果永远不为空：
SELECT Check_Result, COUNT(*) INTO loadResult_Tmp, infoCnt FROM Stat_CPU_All_Info_Tmp WHERE Asset_Id = assetId;
?将COUNT(*)查询出来，即使结果集为空，也会输出一条记录。
?修改后的完整语句：
BEGIN DECLARE assetId VARCHAR(16);DECLARE loadAverage1 FLOAT(10,2) DEFAULT 0;DECLARE loadAverage15 FLOAT(10,2) DEFAULT 0;DECLARE loadAverageCount INT; DECLARE loadResult_Tmp VARCHAR(8192) DEFAULT '';DECLARE infoCnt INT; DECLARE LoadAverageCursorDone INT DEFAULT 0; DECLARE LoadAverageCursor CURSOR FOR SELECT Asset_Id, Load_Average_1, Load_Average_15, Load_Average_Count from Load_Average_Info_Tmp; DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET LoadAverageCursorDone = 1; OPEN LoadAverageCursor; LoadAverageCursorLoop:LOOP FETCH LoadAverageCursor INTO assetId, loadAverage1, loadAverage15, loadAverageCount; IF LoadAverageCursorDone = 1 THEN LEAVE LoadAverageCursorLoop; END IF; SELECT Check_Result, COUNT(*) INTO loadResult_Tmp, infoCnt FROM Stat_CPU_All_Info_Tmp WHERE Asset_Id = assetId; /* ... */ END LOOP; CLOSE LoadAverageCursor;END;?
2.第二种办法是不使用游标，改用临时表替代，方法请见Mysql存储过程优化——使用临时表代替游标
