时间戳

首页 > 解决方案 > 在mysql中使用while循环创建存储过程时出现错误

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; 声明结束 INT；声明 SumofCount BIGINT；声明 block_id V...

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

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

标签： mysqlstored-procedures

解决方案

您的程序中有几个问题。首先，在你的陈述BEGIN之后你有一个不必要的。您可以删除它或将其与. 其次，您需要以 结束循环，在您的情况下将标签添加到该语句。这应该有效：DOWHILEENDWHILEEND WHILEtest

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;

推荐阅读