mysql - 存储过程错误信息

Question

我已经实现了如下存储过程，但是当我尝试应用它时收到以下错误消息:

错误 1064 (42000):您的 SQL 语法有误；查看与您的 MySQL 服务器版本对应的手册，了解在 'WHERE symbol_id = id GROUP BY symbol_id;

附近使用的正确语法

我做了一些调试，发现它是由我试图将结果写入的 @max 变量引起的，但是我没有发现语法有任何问题，任何人都可以提出建议吗？

DROP PROCEDURE IF EXISTS `GENERATE_REPORT`;
DELIMITER $$
CREATE DEFINER=CURRENT_USER PROCEDURE `GENERATE_REPORT`()
BEGIN
    DECLARE id INT;
    DECLARE max INT;
    DECLARE at_end BIT DEFAULT 0;
    DECLARE cur CURSOR
        FOR SELECT symbol_id from trade;
        DECLARE CONTINUE HANDLER
        FOR SQLSTATE '02000' SET at_end=1;

    OPEN cur;
    FETCH cur INTO id;
    WHILE (NOT at_end) DO
        SELECT SUM(quantity) FROM trade INTO **@max** WHERE symbol_id = id GROUP BY symbol_id;
        FETCH cur into id;
    END WHILE;
    CLOSE cur;
END

$$分隔符；

Answer 1

您的 SELECT ... INTO 中的语法不正确:

改变

SELECT SUM(quantity)
FROM trade 
INTO @max -- Incorrect placement
WHERE symbol_id = id
GROUP BY symbol_id;

到

SELECT SUM(quantity)
INTO @max -- Correct placement
FROM trade  
WHERE symbol_id = id
GROUP BY symbol_id;

INTO 应该紧跟在 SELECT 之后和 FROM 之前