Mysql存储过程错误——LOOP和CURSOR

Question

DECLARE oldID int;
DECLARE DONE boolean DEFAULT FALSE;
DECLARE fineCursor CURSOR FOR SELECT `ifLinkID` FROM `image_fine_link` WHERE `image_id` = pimage_id;
DECLARE coarseCursor CURSOR FOR SELECT `icLinkID` FROM `image_coarse_link` WHERE `image_id` = pimage_id;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET DONE = TRUE;


OPEN fineCursor;
OPEN coarseCursor;

read_loop : LOOP
    oldID = NULL;
    FETCH fineCursor INTO oldID;
    IF oldID IS NOT NULL THEN
    CALL update_others(oldID,"delete","fine color",NULL);
    END IF;

    IF !DONE THEN ITERATE read_loop;
    END IF;
    LEAVE read_loop;
END LOOP;

SET DONE = FALSE;

read_lopp : LOOP
    oldID = NULL;
    FETCH coarseCursor INTO oldID;
    IF oldID IS NOT NULL THEN
    CALL update_others(oldID,"delete","coarse color",NULL);
    END IF;

    IF !DONE THEN ITERATE read_loop; 
    END IF;
    LEAVE read_loop;
END LOOP;

CLOSE fineCursor;
CLOSE coarseCursor;

并且出现以下错误，真的很紧急，有什么想法吗？

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '= NULL;
    FETCH fineCursor INTO oldID;
    IF oldID IS NOT NULL THEN
    CALL upd' at line 16 

非常感谢您!~

Answer 1

该特定错误是由于您没有使用 SET 引起的给变量赋值的语句。

改变

oldID = NULL;

至

SET oldID = NULL;