英文:
How to rectify Error #1064 in MariaDB procedure?
问题
这个错误信息表明在你的SQL代码中存在语法错误。问题在于DECLARE语句中的变量定义，应该使用不带@符号的变量名。以下是修正后的代码：
DELIMITER $$CREATE PROCEDURE insert_priority_rows()BEGINDECLARE max_heuristics INT DEFAULT 10;DECLARE heuristic_ID INT;DECLARE cur CURSOR FORSELECT heuristicIDFROM heuristics;DECLARE CONTINUE HANDLERFOR NOT FOUND SET heuristic_ID = 0;DECLARE study_ID INT; -- 移除@符号SELECT MAX(studyID) INTO study_ID FROM study;OPEN cur;REPEATFETCH cur INTO heuristic_ID;IF heuristic_ID = 0 THENLEAVE;END IF;INSERT INTO heuristic_priority (studyID, heuristicID, h_priority)VALUES (study_ID, heuristic_ID, 'm'); -- 移除' 符号UNTIL heuristic_ID = 0 END REPEAT;CLOSE cur;END$$DELIMITER ;
这个修正后的代码应该不再报错，并且能够正确执行。
英文:
DELIMITER $$CREATE PROCEDURE insert_priority_rows()BEGINDECLARE max_heuristics INT DEFAULT 10;DECLARE heuristic_ID INT;DECLARE cur CURSOR FORSELECT heuristicIDFROM heuristics;DECLARE CONTINUE HANDLERFOR NOT FOUND SET heuristic_ID = 0;DECLARE @study_ID INT;SELECT MAX(studyID) INTO @study_ID FROM study;OPEN cur;REPEATFETCH cur INTO heuristic_ID;IF heuristic_ID = 0 THENLEAVE;END IF;INSERT INTO heuristic_priority (studyID, heuristicID, h_priority)VALUES (@study_ID, heuristic_ID, 'm');UNTIL heuristicID = 0 END REPEAT;CLOSE cur;END$$DELIMITER ;
It throws up an error as follows:
> #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use
> near '@study_ID INT;
What should I do to rectify this issue?
答案1
得分: 1
不需要大部分的代码。循环可以用以下查询来替代：
DELIMITER $$CREATE PROCEDURE insert_priority_rows()BEGININSERT INTO heuristic_priority (studyID, heuristicID, h_priority)SELECT m.max_study_id, h.heuristicID, 'm'FROM heuristics AS hCROSS JOIN (SELECT MAX(studyID) as max_study_id FROM study) AS m;END$$
英文:
You don't need most of that code. The loop can be replaced with this query:
DELIMITER $$CREATE PROCEDURE insert_priority_rows()BEGININSERT INTO heuristic_priority (studyID, heuristicID, h_priority)SELECT m.max_study_id, h.heuristicID, 'm'FROM heuristics AS hCROSS JOIN (SELECT MAX(studyID) as max_study_id FROM study) AS m;END$$
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论