mysql - 在 mysql 中使用 while 循环创建存储过程时出现错误

Question

我为特定条件的总和创建了一个存储过程，但出现语法错误。

创建表脚本:

CREATE TABLE count_smaller_coverage (count_records INT(11) ,block_id INT(11))

插入数据:

INSERT INTO count_smaller_coverage 
SELECT '114000','1' UNION
SELECT '112000','2' UNION 
SELECT '98765','3' UNION 
SELECT '78965','4' UNION 
SELECT '4125','5' UNION 
SELECT '123654','6' UNION 
SELECT '78999','7' UNION 
SELECT '89888','8' UNION 
SELECT '99654','9' UNION 
SELECT '75365','10' UNION 
SELECT '25638','11' UNION 
SELECT '85236','12' UNION 
SELECT '65478','13' UNION 
SELECT '65478','14' UNION 
SELECT '85236','15' 

存储过程:

DELIMITER $$
DROP PROCEDURE IF EXISTS test_mysql_while_loop$$
CREATE PROCEDURE test_mysql_while_loop()
BEGIN
DECLARE strat INT;
DECLARE END INT;
DECLARE SumofCount BIGINT;
DECLARE block_id VARCHAR(2000);
SET strat=(SELECT MIN(block_id) FROM count_smaller_coverage);
SET END =(SELECT MAX(block_id) FROM count_smaller_coverage);
CREATE TABLE blocks_parts (block_id VARCHAR(2000), Counts BIGINT);

test: WHILE strat<=END DO

BEGIN

IF SumofCount > 800000 THEN
SET SumofCount=0;
SET block_id = NULL;
END IF;

SET SumofCount=COALESCE(SumofCount,0)+(SELECT count_records FROM count_smaller_coverage WHERE block_id=strat);
SELECT block_id = (COALESCE(block_id + ',', '') + CAST(block_id AS CHAR)) AS id FROM count_smaller_coverage WHERE block_id=strat;

IF SumofCount BETWEEN 800000 AND 1000000 THEN
INSERT INTO blocks_parts(block_id,Counts) VALUES (block_id,SumofCount);
END IF;

IF SumofCount BETWEEN 800000 AND 100000 THEN
    LEAVE test;
END IF;

SET strat=strat+1;

END test;
END$$
DELIMITER ;

错误:

查询:CREATE PROCEDURE test_mysql_while_loop() BEGIN DECLARE strat INT;声明结束 INT；声明 SumofCount BIGINT；声明 block_id V...

错误代码:1064您的 SQL 语法有错误；检查与您的 MySQL 服务器版本相对应的手册，了解使用“near 'test;”的正确语法。END' 在第 33 行

执行时间:0秒传输时间:0秒总时间:0.060 秒

Answer 1

您的程序中有几个问题。首先，您的 WHILE 语句中的 DO 之后有一个不必要的 BEGIN。您可以删除它或将其与 END 匹配。其次，您需要使用 END WHILE 结束 WHILE 循环，在您的情况下，将 test 标签添加到该语句中。这应该有效:

test: WHILE strat<=END DO
    -- BEGIN -- if you put BEGIN here ...
    IF SumofCount > 800000 THEN
    SET SumofCount=0;
    SET block_id = NULL;
    END IF;

    SET SumofCount=COALESCE(SumofCount,0)+(SELECT count_records FROM count_smaller_coverage WHERE block_id=strat);
    SELECT block_id = (COALESCE(block_id + ',', '') + CAST(block_id AS CHAR)) AS id FROM count_smaller_coverage WHERE block_id=strat;

    IF SumofCount BETWEEN 800000 AND 1000000 THEN
    INSERT INTO blocks_parts(block_id,Counts) VALUES (block_id,SumofCount);
    END IF;

    IF SumofCount BETWEEN 800000 AND 100000 THEN
        LEAVE test;
    END IF;

    SET strat=strat+1;
    -- END -- ... you must put END here
END WHILE test;