カーソルにネストされた他のクエリで注意すべき問題
詳細
次のストレージ・プロシージャがあります.
Load_を巡回するカーソルが定義されていますAverage_Info_Tmpテーブルは、その中の1つのデータを取得するごとに取得したassetIdに基づいてStat_を照会するCPU_All_Info_Tmp表.
問題が発生しましたAverage_Info_Tmpテーブルはループを通過せずにループを早めに終了した.
問題はカーソルのselect文です.
カーソル内のこのSELECT文のクエリ結果が空の場合、LoadAverageCursorDone=1となるように'02000'の状態が放出され、ループが終了します.
解決策:
1.問題を引き起こすSELECT文を修正して、クエリ結果がいつまでも空にならないようにする:
COUNT(*)を検索すると、結果セットが空であってもレコードが出力されます.
修正後の完全な文:
2.第2の方法は、カーソルを使用しないで、テンポラリ・テーブルの代わりに変更することです.方法はMysqlストレージ・プロシージャの最適化を参照してください.テンポラリ・テーブルの代わりにテンポラリ・テーブルを使用します.
次のストレージ・プロシージャがあります.
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テーブルは、その中の1つのデータを取得するごとに取得したassetIdに基づいてStat_を照会するCPU_All_Info_Tmp表.
問題が発生しましたAverage_Info_Tmpテーブルはループを通過せずにループを早めに終了した.
問題はカーソルのselect文です.
SELECT Check_Result INTO loadResult_Tmp FROM Stat_CPU_All_Info_Tmp WHERE Asset_Id = assetId;
カーソル内のこのSELECT文のクエリ結果が空の場合、LoadAverageCursorDone=1となるように'02000'の状態が放出され、ループが終了します.
解決策:
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.第2の方法は、カーソルを使用しないで、テンポラリ・テーブルの代わりに変更することです.方法はMysqlストレージ・プロシージャの最適化を参照してください.テンポラリ・テーブルの代わりにテンポラリ・テーブルを使用します.