在尝试执行存储过程循环时出现错误- MySQL

Question

出现以下错误:

DELIMITER $$
CREATE PROCEDURE updateA()
begin
DECLARE incr INT Default 1;
a_loop: LOOP
update `A_Request` SET `requestRegion` = 
(SELECT
GROUP_CONCAT( DISTINCT
(CASE
WHEN B_ProductLine.subRegion in (4,5) THEN 108
WHEN B_ProductLine.subRegion in (6,7) THEN 109
WHEN B_ProductLine.subRegion in (195,201) THEN 111
ELSE null
END) SEPARATOR '; '
)
as `newRegion`
FROM `B_ProductLine` where B_ProductLine.requestId in (Select `A_Request`.bId FROM (SELECT * from `A_Request` where `A_Request`.`id` = incr) as `derived`) group by B_ProductLine.requestId) WHERE `A_Request`.`id` = incr;

SET incr = incr + 1;
IF incr = 77 THEN
LEAVE a_loop;
END IF;
END LOOP a_loop;
END $$;

错误如下:查询为空。当在存储过程外单独运行查询时，我根本没有遇到任何问题

Answer 1

你必须用分号结束所有有命令的行并且你在末尾遗漏了一个分隔符

DELIMITER $$
CREATE PROCEDURE updateA()
begin
DECLARE incr INT Default 1;
a_loop: LOOP
update `A_Request` SET `requestRegion` = 
(SELECT
GROUP_CONCAT( DISTINCT
(CASE
WHEN B_ProductLine.subRegion in (4,5) THEN 108
WHEN B_ProductLine.subRegion in (6,7) THEN 109
WHEN B_ProductLine.subRegion in (195,201) THEN 111
ELSE null
END) SEPARATOR '; '
)
as `newRegion`
FROM `B_ProductLine` where B_ProductLine.requestId in (Select `A_Request`.bId FROM (SELECT * from `A_Request` where `A_Request`.`id` = incr) as `derived`) group by B_ProductLine.requestId) WHERE `A_Request`.`id` = incr;
SET incr = incr + 1;
IF incr = 77 THEN
LEAVE a_loop;
END IF;
END LOOP a_loop;
END $$
DELIMITER ;