游戏服务器mysql存储过程使用select...into...variables提前退出光标循环原因探究
发表于2015-07-27
【首先请大家看一下下面的脚本】
label : BEGIN
...
DECLARE tmp_Id BIGINT DEFAULT 0;
DECLARE tmp_Rank INT DEFAULT 0;
DECLARE tmp_old_Rank INT DEFAULT 0;
DECLARE Done INT DEFAULT 0;
DECLARE cur_Table2 CURSOR FOR SELECT Id, Rank FROM tmp_Table2;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET Done = 1;
...
SET Done = 0;
SET @cnt = 0;
SELECT COUNT(*) FROM tmp_Table2;
OPEN cur_Table2;
table2Loop : LOOP
FETCH cur_Table2 INTO tmp_Id, tmp_Rank;
IF Done = 1 THEN
SELECT @cnt;
LEAVE table2Loop;
END IF;
SET tmp_old_Rank = 0;
SELECT Rank INTO tmp_old_Rank FROM Table1 WHERE Id = tmp_Id;
IF tmp_Rank > tmp_old_Rank THEN
UPDATE Table1 SET Rank = tmp_Rank WHERE Id = tmp_Id;
END IF;
...
END LOOP table2Loop;
CLOSE cur_Table2;
...
END
【脚本执行结果】
9784
7408
【错误剖析】
SELECT...INTO...VARIABLES语句可能会触发sqlstate=02000，从而到“DECLARE CONTINUE HANDLER FOR NOT FOUND SET Done = 1;”被执行，提前退出循环。
SQLSTATE变为’02000‘在下列情况下会发生：
SELECT INTO 语句或 INSERT 语句的子查询的结果为空。
在搜索的 UPDATE 或 DELETE 语句内标识的行数为零。
在 FETCH 语句中引用的游标位置处于结果表最后一行之后。
【最佳修改方法】
label : BEGIN
...
DECLARE tmp_Id BIGINT DEFAULT 0;
DECLARE tmp_Rank INT DEFAULT 0;
DECLARE tmp_old_Rank INT DEFAULT 0;
DECLARE Done INT DEFAULT 0;
DECLARE cur_Table2 CURSOR FOR SELECT Id, Rank FROM tmp_Table2;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET Done = 1;
...
SET Done = 0;
SET @cnt = 0;
SELECT COUNT(*) FROM tmp_Table2;
OPEN cur_Table2;
table2Loop : LOOP
FETCH cur_Table2 INTO tmp_Id, tmp_Rank;
IF Done = 1 THEN
SELECT @cnt;
LEAVE table2Loop;
END IF;
SET tmp_old_Rank = 0;
SELECT Rank INTO tmp_old_Rank FROM Table1 WHERE Id = tmp_Id;
IF Done = 1 THEN
SET Done = 0;
ELSE
IF tmp_Rank > tmp_old_Rank THEN
UPDATE Table1 SET Rank = tmp_Rank WHERE Id = tmp_Id;
END IF;
END IF;
...
END LOOP table2Loop;
CLOSE cur_Table2;
...
END
【其他修改方法】
label : BEGIN
...
DECLARE tmp_Id BIGINT DEFAULT 0;
DECLARE tmp_Rank INT DEFAULT 0;
DECLARE tmp_old_Rank INT DEFAULT 0;
DECLARE Done INT DEFAULT 0;
DECLARE cur_Table2 CURSOR FOR SELECT Id, Rank FROM tmp_Table2;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET Done = 1;
...
SET Done = 0;
SET @cnt = 0;
SELECT COUNT(*) FROM tmp_Table2;
OPEN cur_Table2;
table2Loop : LOOP
FETCH cur_Table2 INTO tmp_Id, tmp_Rank;
IF Done = 1 THEN
SELECT @cnt;
LEAVE table2Loop;
END IF;
SET tmp_old_Rank = 0;
SELECT COUNT(*) INTO @tmpCount FROM Table1 WHERE Id = tmp_Id;
IF @tmpCount = 1 THEN
SELECT Rank INTO tmp_old_Rank FROM Table1 WHERE Id = tmp_Id;
IF tmp_Rank > tmp_old_Rank THEN
UPDATE Table1 SET Rank = tmp_Rank WHERE Id = tmp_Id;
END IF;
...
END IF;
...
END LOOP table2Loop;
CLOSE cur_Table2;
...
END