游标中嵌套有其他查询时要注意的问题 博客分类: Mysql MySQL游标存储过程
程序员文章站
2024-03-23 17:21:04
...
有如下存储过程:
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存储过程优化——使用临时表代替游标