MySQL在创建存储过程的时候,语法正确却提示You have an error in your SQL syntax

　　我在使用MySQL工具编写MySQL存储过程的时候,明明语法正确,但是却一直提示You have an error in your SQL syntax。

　　比如下面一段代码

 1 CREATE PROCEDURE demo_pro()
 2 BEGIN
 3     DECLARE doned BOOLEAN;
 4     DECLARE addr varchar(20);
 5     DECLARE demo_cur CURSOR FOR SELECT detail from address;
 6     DECLARE CONTINUE HANDLER FOR NOT FOUND SET doned = True;
 7     SET doned = False;
 8     OPEN demo_cur;
 9     demo_cur:LOOP
10         FETCH demo_cur into addr;
11         IF doned THEN
12             LEAVE demo_cur;
13         ELSE
14             insert into demo(demo_value) values(addr);
15         END IF;
16     END LOOP;
17     CLOSE demo_cur;
18 END;

　　找了半天才找到解决方案,原来不是语法的错误，是MYSQL语法解析器的原因。MYSQL 解析器解析遇到“;”分号，就结束本次执行，所以就造成了很多语法错误。解决方案是:

 1 DELIMITER |  #结束符号,可以根据自己的需求,改成 $也可以
 2 CREATE PROCEDURE demo_pro()
 3 BEGIN
 4     DECLARE doned BOOLEAN;
 5     DECLARE addr varchar(20);
 6     DECLARE demo_cur CURSOR FOR SELECT detail from address;
 7     DECLARE CONTINUE HANDLER FOR NOT FOUND SET doned = True;
 8     SET doned = False;
 9     OPEN demo_cur;
10     demo_cur:LOOP
11         FETCH demo_cur into addr;
12         IF doned THEN
13             LEAVE demo_cur;
14         ELSE
15             insert into demo(demo_value) values(addr);
16         END IF;
17     END LOOP;
18     CLOSE demo_cur;
19 END;|
20 DELIMITER ;

 

posted on 2014-04-15 15:00  Arts&Crafts  阅读(2729)  评论(0)  编辑  收藏  举报

导航