我在使用MySQL工具编写MySQL存储过程的时候,明明语法正确,但是却一直提示You have an error in your SQL syntax。
比如下面一段代码
CREATE PROCEDURE demo_pro()
BEGIN
DECLARE doned BOOLEAN;
DECLARE addr varchar(20);
DECLARE demo_cur CURSOR FOR SELECT detail from address;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET doned = True;
SET doned = False;
OPEN demo_cur;
demo_cur:LOOP
FETCH demo_cur into addr;
IF doned THEN
LEAVE demo_cur;
ELSE
insert into demo(demo_value) values(addr);
END IF;
END LOOP;
CLOSE demo_cur;
END;
找了半天才找到解决方案,原来不是语法的错误,是MYSQL语法解析器的原因。MYSQL 解析器解析遇到“;”分号,就结束本次执行,所以就造成了很多语法错误。解决方案是:
DELIMITER | #结束符号,可以根据自己的需求,改成 $也可以
CREATE PROCEDURE demo_pro()
BEGIN
DECLARE doned BOOLEAN;
DECLARE addr varchar(20);
DECLARE demo_cur CURSOR FOR SELECT detail from address;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET doned = True;
SET doned = False;
OPEN demo_cur;
demo_cur:LOOP
FETCH demo_cur into addr;
IF doned THEN
LEAVE demo_cur;
ELSE
insert into demo(demo_value) values(addr);
END IF;
END LOOP;
CLOSE demo_cur;
END;|
DELIMITER ;