MySQL 存储过程

发布于 2024-08-09 09:37:51 字数 544 浏览 8 评论 0原文

我有 MS SQL Server 背景。使用 MySQL 和 NaviCat 8 管理工具开发一个新项目。好的，问题来了。通常，在 MS 领域工作时，如果我想更新一些数据，我会使用存储过程来执行此操作：

Drop Procedure spNew
Create Procedure spNew (@P_Param)

UPDATE Table
SET Field = 'some value'
WHERE ID = @P_Param

我尝试在 NaviCat 中执行相同的逻辑。我定义了参数，(IN '@P_Param' int)

在我放置的定义中：

BEGIN
     UPDATE Table
     SET Field = 'some value'
     WHERE ID = @P_Param
END;

当我尝试保存存储过程时，出现此错误： “1064 - 你的 SQL 语法有错误，等等，等等”

任何人都可以至少指出我正确的方向吗？

谢谢。

原文

I'm coming from a MS SQL Server background.
Working on a new project using MySQL with NaviCat 8 Admin tools.
Ok, here's the question.
Normally when working in MS land if I want to update some data I use a stored procedure to do this:

Drop Procedure spNew
Create Procedure spNew (@P_Param)

UPDATE Table
SET Field = 'some value'
WHERE ID = @P_Param

I am trying to do this same logic from within NaviCat.
I defined the Parameter, (IN '@P_Param' int)

In the Definition i placed:

BEGIN
     UPDATE Table
     SET Field = 'some value'
     WHERE ID = @P_Param
END;

When I try and save the stored procedure, i'm getting this error:
"1064 - You have an error in your SQL syntax, blah, blah, blah"

Can anyone at least point me in the right direction?

Thanks.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

嘿嘿嘿 2024-08-16 09:37:51

CREATE PROCEDURE spNew(P_Param INT)
BEGIN
     UPDATE Table
     SET Field = 'some value'
     WHERE ID = P_Param;
END;

请注意，MySQL 语法和整体思想与SQL Server非常不同。

您可能还需要设置分隔符：

DELIMITER $

CREATE PROCEDURE spNew(P_Param INT)
BEGIN
     UPDATE Table
     SET Field = 'some value'
     WHERE ID = P_Param;
END;
$

DELIMITER ;

顺便说一句，我假设您实际上并未将表称为“Table”，因为它是保留字。

如果这样做，您需要将其括在反引号中，如下所示：

DELIMITER $

CREATE PROCEDURE spNew(P_Param INT)
BEGIN
     UPDATE `Table`
     SET    `Field` = 'some value'
     WHERE  `ID` = P_Param;
END;
$

DELIMITER ;

CREATE PROCEDURE spNew(P_Param INT)
BEGIN
     UPDATE Table
     SET Field = 'some value'
     WHERE ID = P_Param;
END;

Note that MySQL syntax and overall ideology are very different from those of SQL Server.

You may also need to set delimiter:

DELIMITER $

CREATE PROCEDURE spNew(P_Param INT)
BEGIN
     UPDATE Table
     SET Field = 'some value'
     WHERE ID = P_Param;
END;
$

DELIMITER ;

BTW, I'm assuming you don't actually call your table "Table", since it's a reserved word.

If you do, you need to enclose it into backticks like this:

DELIMITER $

CREATE PROCEDURE spNew(P_Param INT)
BEGIN
     UPDATE `Table`
     SET    `Field` = 'some value'
     WHERE  `ID` = P_Param;
END;
$

DELIMITER ;

回复收藏 0 原文

寄居人 2024-08-16 09:37:51

MySQL 存储过程的参数在声明或使用时不以 @ 为前缀或加引号。但是，局部变量以 @ 为前缀。

尝试：

DROP PROCEDURE IF EXISTS spNew;
CREATE PROCEDURE spNew(IN P_Param INT)
BEGIN
     UPDATE Table
     SET Field = 'some value'
     WHERE ID = P_Param
END;

Parameters to MySQL stored procedures aren't prefixed with @ or quoted in either the declaration or when used. Local variables are prefixed with @, however.

Try:

DROP PROCEDURE IF EXISTS spNew;
CREATE PROCEDURE spNew(IN P_Param INT)
BEGIN
     UPDATE Table
     SET Field = 'some value'
     WHERE ID = P_Param
END;

回复收藏 0 原文

~没有更多了~