在MySQL中创建存储过程时出现SQL语法错误

I have a hard time locating an error when trying to create a stored procedure in mysql.

尝试在mysql中创建存储过程时,我很难找到错误。

If I run every single line of the procedure independently, everything works just fine.

如果我独立运行程序的每一行,一切正常。

CREATE PROCEDURE cms_proc_add_child 
(
    param_parent_id INT, param_name CHAR(255),
    param_content_type CHAR(255)
)
BEGIN
    SELECT @child_left := rgt FROM cms_tree WHERE id = param_parent_id;
    UPDATE cms_tree SET rgt = rgt+2 WHERE rgt >= @child_left;
    UPDATE cms_tree SET lft = lft+2 WHERE lft >= @child_left;
    INSERT INTO cms_tree (name, lft, rgt, content_type) VALUES 
    (
        param_name,
        @child_left,
        @child_left+1,
        param_content_type
    );
END

I get the following (helpful) error:

我得到以下(有用)错误:

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 3

错误1064(42000):您的SQL语法有错误;检查与MySQL服务器版本对应的手册,以便在第3行的''附近使用正确的语法

I just don't know where to start debugging, as every single one of these lines is correct.

我只是不知道从哪里开始调试,因为这些行中的每一行都是正确的。

Do you have any tips?

你有什么建议吗?

3 个解决方案

#1

As line 3 contains the first ; perhaps you have a problem with your delimiters.

第3行包含第一个;也许你的分隔符有问题。

See http://dev.mysql.com/doc/refman/5.0/en/stored-programs-defining.html

DELIMITER //
CREATE PROCEDURE dorepeat(p1 INT)
BEGIN
    SET @x = 0;
    REPEAT SET @x = @x + 1; UNTIL @x > p1 END REPEAT;
END//
DELIMITER ;

#2

You never declare your @child_left variable.

你永远不会声明你的@child_left变量。

#3

Thanks, near '' at line 3 was my problem and the delimiter statement fixed it! I always want things to make sense and this does. As the '' indicates it's at the end of the procedure, but no END statement was found thus the syntax error. And I wondered why I kept seeing a lot of people using the delimiter statement. I see the light!

谢谢,在第3行附近是我的问题,分隔符语句修复了它!我总是希望事情有意义,这样做。由于''表示它在过程结束时,但没有找到END语句因此语法错误。我想知道为什么我一直看到很多人使用分隔符语句。我看见了灯光!

#1