欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

游标中嵌套有其他查询时要注意的问题 博客分类: 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存储过程优化——使用临时表代替游标