MySQL DECLARE/SELECT INTO 语法错误？

Question

老实说，我不知道我在这里做错了什么。

Routine Name: procInsertName
Type: PROCEDURE
Parameters: IN Name VARCHAR 30 Charset

DECLARE tempNameID INT;

SELECT nameID
INTO tempNameID
FROM name
WHERE value = @Name;

IF tempNameID IS NULL THEN

    INSERT INTO name (value)
    VALUES (@Name);

END IF;

Is deterministic: unchecked
Definer: <blank>
Security type: DEFINER
SQL data access: CONTAINS SQL
Comment: <blank>

我遇到了以下错误:

One or more errors have occurred while processing your request:

The following query has failed: "CREATE PROCEDURE `procInsertName`(IN `Name` VARCHAR(30)) NOT DETERMINISTIC CONTAINS SQL SQL SECURITY DEFINER DECLARE   tempNameID INT;

SELECT  nameID
INTO    tempNameID
FROM    name
WHERE   value = @Name;

IF tempNameID IS NULL THEN

    INSERT INTO name (value)
    VALUES (@Name);

END IF;"

MySQL said: #1064 - 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 'DECLARE  tempNameID INT;

SELECT  nameID
INTO    tempNameID
FROM    name
WHERE   valu' at line 1

任何帮助将不胜感激，我花了过去 30 分钟在网上查找，但找不到任何东西。谢谢。

Answer 1

我认为错误可能出在@符号中...您一定有 SQL Server 背景； MySQL 不使用 @ 表示法来表示变量，因此您的参数实际上只是称为 Name。尝试将 Name 参数重命名为 p_Name，然后将正文中的 2 个 @Name 实例替换为 p_Name您的存储过程。

即像这样:

CREATE PROCEDURE `procInsertName`(IN `p_Name` VARCHAR(30)) NOT DETERMINISTIC CONTAINS SQL SQL SECURITY DEFINER 
DECLARE   tempNameID INT;

SELECT  nameID
INTO    tempNameID
FROM    name
WHERE   value = p_Name;

IF tempNameID IS NULL THEN

    INSERT INTO name (value)
    VALUES (p_Name);

END IF;