在MySQL存储函数中声明整数变量时出错 [英] Error declaring integer variable inside MySQL stored function

问题描述

我在尝试在MySQL中声明一个新的存储函数时发生错误（服务器版本：5.5.13）基本上，我有一个大表根据它们的启动方式对字符串进行分类。我的函数接受一个字符串（来自用户输入），然后通过搜索数据库的分类来告诉你该字符串的分类。这有点像LIKE查询，除了相反，因为它是包含完整字符串的用户输入，并且数据库包含要搜索的字符串。希望这是有道理的！

它背后的概念和逻辑工作正常，因为我已经用PHP编写/开发了它，并且它运行得非常好，但是当试图将其转换为一个存储的函数，我从MySQL获取错误。该函数的代码是：

 定界符
 
 DROP FUNCTION如果存在get_string_class $ 
 CREATE FUNCTION get_string_class（mystring VARCHAR（15））
 
 RETURNS VARCHAR（15）
 READS SQL DATA 
 BEGIN 
 
 DECLARE i INT; 
 SET i = 2; 
 
 DECLARE mystringlength INT; 
 SET mystringlength = LENGTH（mystring）; 
 
 DECLARE segment VARCHAR（15）; 
 DECLARE String_Class VARCHAR（15）; 
 SET String_Class = NULL; 
 
 WHILE i< = mystringlength DO 
 
 SET segment = LEFT（mystring，i）; 
 
 SET String_Class =（SELECT String_Class FROM string_class_list WHERE String_Begins = segment）; 
 
 IF SELECT FOUND_ROWS（）= 1 THEN 
 RETURN String_Class 
 END IF; 
 
 i = i + 1; 
 
 END WHILE; 
 
 RETURN String_Class; 
 
 END $ 
分隔符; 
  

我得到这个错误：

 ＃1064  - 你的SQL语法有错误;检查相应
的手册到你的MySQL服务器版本，以便在
附近使用正确的语法DECLARE mystringlength INT; SET mystringlength = LENGTH（mystring）; 
 DECLARE在第10行的segm 
  

我已经做了很多尝试和弄清楚我要去哪里错了。我甚至完全删除了循环来测试它，但仍然得到相同的错误。有谁知道我做错了什么地方声明INT变量？这可能是一些令人难以置信的基本功能......！

非常感谢您的帮助。

有一些语法错误 -

其中之一是 - 所有声明必须在BEGIN ... END子句的开头

  DELIMITER $ 
 
 DROP FUNCTION如果存在get_string_class $ 
 CREATE FUNCTION get_string_class（mystring VARCHAR（15））
 
 RETURNS VARCHAR（15）
 READS SQL DATA 
 BEGIN 
 
 DECLARE i INT; 
 DECLARE segment VARCHAR（15）; 
 DECLARE String_Class VARCHAR（15）; 
 DECLARE mystringlength INT; 
 
 SET i = 2; 
 SET mystringlength = LENGTH（mystring）; 
 SET String_Class = NULL; 
 
 WHILE i< = mystringlength DO 
 
 SET segment = LEFT（mystring，i）; 
 
 SET String_Class =（SELECT String_Class FROM string_class_list WHERE String_Begins = segment）; 
 
 IF（SELECT FOUND_ROWS（））= 1 THEN 
 RETURN String_Class; 
 END IF; 
 
 SET i = i + 1; 
 
 END WHILE; 
 
 RETURN String_Class; 
 
 END $ 
 DELIMITER; 
  

I'm getting an error when trying to declare a new stored function in MySQL (Server version: 5.5.13)

Basically, I have a large table which classifies strings depending on how they start. My function takes a string (from user input) and then tells you the classification of that string by searching the database for the classification. It's a bit like a LIKE query, except in reverse as it's the user input that contains the full string and the database contains the string being searched for. Hope that makes sense!

The concept and logic behind it work fine as I've written/developed this in PHP and it works beautifully, however when trying to translate this into a stored function I'm getting an error from MySQL. The code of the function is:

delimiter $

DROP FUNCTION IF EXISTS get_string_class$
CREATE FUNCTION get_string_class(mystring VARCHAR(15))

RETURNS VARCHAR(15)
READS SQL DATA
BEGIN

DECLARE i INT;
SET i = 2;

DECLARE mystringlength INT;
SET mystringlength = LENGTH(mystring);

DECLARE segment VARCHAR(15);
DECLARE String_Class VARCHAR(15);
SET String_Class = NULL;

WHILE i <= mystringlength DO

   SET segment = LEFT(mystring, i);

   SET String_Class = (SELECT String_Class FROM string_class_list WHERE String_Begins = segment);

   IF SELECT FOUND_ROWS() = 1 THEN
      RETURN String_Class
   END IF;

   i = i + 1;

END WHILE;

RETURN String_Class;

END$
delimiter ;

I'm getting this error:

#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 mystringlength INT; SET mystringlength = LENGTH(mystring);
DECLARE segm' at line 10 

I've done lots of playing around to trying and work out where I'm going wrong. I've even stripped out the loop altogether to test it, but still get the same error. Does anyone know what I've done wrong where declaring that INT variable? It's probably something incredibly basic...!

Thanks very much in advance.

解决方案

There were some syntax errors -

One of them is - all declarations must be at the begining of the BEGIN...END clause.

DELIMITER $

DROP FUNCTION IF EXISTS get_string_class$
CREATE FUNCTION get_string_class(mystring VARCHAR(15))

RETURNS VARCHAR(15)
READS SQL DATA
BEGIN

DECLARE i INT;
DECLARE segment VARCHAR(15);
DECLARE String_Class VARCHAR(15);
DECLARE mystringlength INT;

SET i = 2;
SET mystringlength = LENGTH(mystring);
SET String_Class = NULL;

WHILE i <= mystringlength DO

   SET segment = LEFT(mystring, i);

   SET String_Class = (SELECT String_Class FROM string_class_list WHERE String_Begins = segment);

   IF (SELECT FOUND_ROWS()) = 1 THEN
      RETURN String_Class;
   END IF;

   SET i = i + 1;

END WHILE;

RETURN String_Class;

END$
DELIMITER ;

这篇关于在MySQL存储函数中声明整数变量时出错的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！