mysql - 使用声明的变量返回函数时出错

Question

已关闭。这个问题是 not reproducible or was caused by typos 。目前不接受答案。

---

这个问题是由拼写错误或无法再重现的问题引起的。虽然类似的问题可能是 on-topic在这里，这个问题的解决方式不太可能帮助 future 的读者。

已关闭 3 年前。

Improve this question

因此，我尝试创建一个函数来创建可变的增长百分比，然后使用该值来生成来年一个月的预测。这是我的代码:

CREATE FUNCTION getPredictedSales(forecastmonth int(2))
RETURNS DOUBLE(6,2)
BEGIN
SELECT @percentincrease = (SELECT (SUM(s19.totalsales) / SUM(s18.totalsales))
FROM Sales2018 s18
INNER JOIN Sales2019 s19 ON s18.month = s19.month AND s18.shopname = s19.shopname
WHERE s18.month = forecastmonth)

RETURN (SELECT (SUM(s19.totalsales)*(@percentincrease)) FROM Sales2019 s19 WHERE month = 1));

END

这是错误:

#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 'RETURN (SELECT (SUM(s19.totalsales)*(@percentincrease)) FROM Sales2019 s19 WHERE' at line 9

Answer 1

您必须在每个语句末尾添加分号。而且你的返回率很高。

所以看起来应该是这样。

DELIMITER $$
CREATE FUNCTION getPredictedSales(forecastmonth int(2))
RETURNS DOUBLE(6,2)
BEGIN
  SELECT @percentincrease = (SELECT (SUM(s19.totalsales) / SUM(s18.totalsales))
  FROM Sales2018 s18
  INNER JOIN Sales2019 s19 ON s18.month = s19.month AND s18.shopname = s19.shopname
  WHERE s18.month = forecastmonth);

  RETURN (SELECT (SUM(s19.totalsales)*(@percentincrease)) FROM Sales2019 s19 WHERE month = 1);
END $$
DELIMITER ;