MySQL 语法错误 - END IF 行

Question

我尝试创建一个包含 if 语句的存储过程。我复制自:https://dev.mysql.com/doc/refman/5.7/en/local-variable-scope.html

但是我在 END IF 上得到了以下错误；在''附近:

DROP PROCEDURE IF EXISTS `myProc`; 
CREATE DEFINER=`root`@`%` PROCEDURE `myProc`(
   IN in_userId int,
   IN in_projectId int
)
BEGIN
    DECLARE tmp_courseId int;
    DECLARE done TINYINT DEFAULT 0;
    DECLARE cursorProjectCourse CURSOR FOR SELECT CourseId FROM XC_PROJECT_COURSE where projectId = in_projectId ;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

    OPEN cursorProjectCourse;

    read_loop: LOOP

        FETCH FROM cursorProjectCourse INTO tmp_courseId;

        IF done = 1 THEN LEAVE read_loop;

        END IF;

        SELECT tmp_courseId, in_userId; 
    END LOOP;
    CLOSE cursorProjectCourse;
END;

有人知道我哪里出错了吗？

确切的错误信息:

SQL Error [1064] [42000]: 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 '' at line 19 SQL Error

MySQL版本:5.5.46

感谢您的帮助!

Answer 1

我找到了解决方案。我必须在第一个语句和结尾设置 DELIMITER $$ DELIMITER ;

DELIMITER $$;
DROP PROCEDURE IF EXISTS `myProc`; $$
CREATE DEFINER=`root`@`%` PROCEDURE `myProc`(
   IN in_userId int,
   IN in_projectId int
)
BEGIN
    DECLARE tmp_courseId int;
    DECLARE done TINYINT DEFAULT 0;
    DECLARE cursorProjectCourse CURSOR FOR SELECT CourseId FROM XC_PROJECT_COURSE where projectId = in_projectId ;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

    OPEN cursorProjectCourse;

    read_loop: LOOP

        FETCH FROM cursorProjectCourse INTO tmp_courseId;

        IF done = 1 THEN LEAVE read_loop;

        END IF;

        SELECT tmp_courseId, in_userId; 
    END LOOP;
    CLOSE cursorProjectCourse;
END;$$
DELIMITER ;

It is important to set the keyword on the first position in line. If there is a blank on the first position, the error above will be thrown.