英文:
How to rectify Error #1064 in MariaDB procedure?
问题
这个错误信息表明在你的SQL代码中存在语法错误。问题在于DECLARE语句中的变量定义，应该使用不带@符号的变量名。以下是修正后的代码：
DELIMITER $$
CREATE PROCEDURE insert_priority_rows()
BEGIN
DECLARE max_heuristics INT DEFAULT 10;
DECLARE heuristic_ID INT;
DECLARE cur CURSOR FOR
SELECT heuristicID
FROM heuristics;
DECLARE CONTINUE HANDLER
FOR NOT FOUND SET heuristic_ID = 0;
DECLARE study_ID INT; -- 移除@符号
SELECT MAX(studyID) INTO study_ID FROM study;
OPEN cur;
REPEAT
FETCH cur INTO heuristic_ID;
IF heuristic_ID = 0 THEN
LEAVE;
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()
BEGIN
DECLARE max_heuristics INT DEFAULT 10;
DECLARE heuristic_ID INT;
DECLARE cur CURSOR FOR
SELECT heuristicID
FROM heuristics;
DECLARE CONTINUE HANDLER
FOR NOT FOUND SET heuristic_ID = 0;
DECLARE @study_ID INT;
SELECT MAX(studyID) INTO @study_ID FROM study;
OPEN cur;
REPEAT
FETCH cur INTO heuristic_ID;
IF heuristic_ID = 0 THEN
LEAVE;
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()
BEGIN
INSERT INTO heuristic_priority (studyID, heuristicID, h_priority)
SELECT m.max_study_id, h.heuristicID, 'm'
FROM heuristics AS h
CROSS 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()
BEGIN
INSERT INTO heuristic_priority (studyID, heuristicID, h_priority)
SELECT m.max_study_id, h.heuristicID, 'm'
FROM heuristics AS h
CROSS JOIN (SELECT MAX(studyID) as max_study_id FROM study) AS m;
END$$
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论