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屋!