MYSQL存储过程:变量声明和条件语句 [英] MYSQL Stored Procedures: Variable Declaration and Conditional Statements

问题描述

我查看了许多教程，手册和文档，但是仍然无法使用.

I have looked over numerous tutorials, manuals and documentations, but I still can not get this to work.

我正在尝试使用phpMyAdmin创建存储过程.

I am trying to create a stored procedure using phpMyAdmin.

我似乎在这里找不到错误，sql错误是如此含糊...

I cant seem to find the errors here, the sql errors are so vague...

CREATE PROCEDURE insertToonOneShot(IN locale CHAR(2), IN name VARCHAR(16), IN realm VARCHAR(24), IN faction CHAR(1), IN toon_level INT, IN class_name INT)
BEGIN
DECLARE @realmID INT;
DECLARE @classID INT;
DECLARE @toonID INT;
SET @realmID = SELECT id FROM realms WHERE realms.name = realm;
SET @classID = SELECT id FROM classes WHERE classes.name = class_name;
IF NOT @realmID IS NULL AND NOT @classID IS NULL AND @toonID IS NULL THEN
INSERT INTO 
toon (`locale`, `name`, `realm_id`, `faction`, `level`, `class_id`)
VALUES
(locale, name, @realmID, faction, toon_level, @classID);
END IF;
END;

我现在遇到的错误是:

#1064-您的SQL语法有错误；检查手册 对应于您的MySQL服务器版本以使用正确的语法 @realmID INT附近；宣告@classID INT;声明@toonID INT;设置@rea 在第3行

#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 @realmID INT; DECLARE @classID INT; DECLARE @toonID INT; SET @rea at line 3

可能是我不得不做的更令人沮丧的事情之一...

Probably one of the more frustrating things I have ever had to do...

我在网上看到了许多教程，这些教程在变量声明中使用@符号显示，而另一些则没有使用它，我什至还看到了一些使用VAR而不是DECLARE的教程.正确的语法是什么?...

I have seen many tutorials online that show using the @ symbol in variable declaration, and others not using it, I have even seen some that use VAR instead of DECLARE. What is the right syntax?...

推荐答案

这可以解决问题:

CREATE PROCEDURE insertToonOneShot(IN locale CHAR(2), IN name VARCHAR(16), IN realm VARCHAR(24), IN faction CHAR(1), IN toon_level INT, IN class_name VARCHAR(12))
BEGIN
SELECT @realmID := id FROM realms WHERE realms.name = realm;
SELECT @classID := id FROM classes WHERE classes.name = class_name;
SELECT @toonID := id FROM toon WHERE toon.name = name AND toon.realm_id = @realmID;
IF NOT @realmID IS NULL AND NOT @classID IS NULL AND @toonID IS NULL
THEN 
INSERT INTO toon (`locale`, `name`, `class_id`, `realm_id`, `faction`, `level`)
VALUES (locale, name, @classID, @realmID, faction, toon_level);
END IF;
END;
//

显然，不需要声明语句……谁知道呢?

Apparently the declare statements were not required... Who would have known?

感谢Gordon Linoff向我指出了正确的方向.

Thanks to Gordon Linoff for pointing me in the right direction.

这篇关于MYSQL存储过程:变量声明和条件语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！