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

我创建了一个存储过程，用于总和条件的求和但得到语法错误。

创建表脚本：

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; DECLARE结束INT； DECLARE SumofCount BIGINT；宣告block_id V ...

错误代码：1064
您的SQL语法有误。检查与您的MySQL服务器版本相对应的手册，以获取在'test'附近使用的正确语法；
在第33行结束

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

最佳答案

您的程序中有几个问题。首先，在BEGIN语句中的DO之后有一个不必要的WHILE。您可以删除它或将其与END匹配。其次，您需要以WHILE结束END 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;

关于mysql - 在MySQL中使用while循环创建存储过程时出现错误，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/56323058/