set语句中的MySQL脚本

Question

我从O.jones那里得到一个提示，然后去创建一个存储函数。

这就是我创造的：

分隔符$$创建函数change_int(colres (500))返回INT(11)

 BEGIN

    DECLARE res int(11);
    DECLARE leng int(11);
    DECLARE newres int(11);
    DECLARE mult int(11);
    DECLARE temp1 int(11);
    DECLARE temp2 int(11);  

SET res = CAST(colres AS UNSIGNED);
SET leng = CHAR_LENGTH(CAST( colres AS CHAR)); 
SET newres = 0;
SET mult = 1;
SET temp1 = 0;
SET temp2 = 0;


WHILE (res > 0) DO

    SET temp1 = MOD(res , 10 );
    SET res = (res DIV 10);
    SET temp2 = MOD(res , 10 );
    SET newres = (newres +((temp1 + temp2 ) * mult));       
    SET mult = mult*10;
END WHILE;
SET newres = SUBSTRING (newres, 1, leng );
RETURN newres;  
END $$
DELIMITER ;

但是，当我在第6行上运行它时，我会出错：

#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 ''res' = CAST(colres AS CHAR)' at line 6

添加了一个分隔符，新错误：

 #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 'SET newres = SUBSTRING (newres, 1, leng );
    RETURN newres;    
    END' at line 27

现在起作用了。但我无法唤起她：

运行测试时：

SHOW FUNCTION STATUS;

而且它的存在。

当我试图像这样运行它时：

UPDATE `table` SET `col1` = function_name(`col1`);

也曾尝试过：

UPDATE `table` SET `col1` = db.function_name(`col1`);

不走运。

Answer 1

您在END WHILE之前缺少了列。一定是END WHILE;

工作实例