mysql - 在LOOP语句后插入和更新命令

我正在尝试在MySql中创建一个存储过程，就像下面的代码一样，它需要在END LOOP标记后继续进行进一步的更新。

不知何故，END LOOP标记后的插入命令未执行，有人可以告诉我为什么吗？我该如何解决呢？

BEGIN
/**Constant variables*/
DECLARE constX  FLOAT DEFAULT 2.660;
DECLARE constMR FLOAT DEFAULT 3.268;

/**Variables for tbSell*/
DECLARE seID, prodID INTEGER DEFAULT 0;
DECLARE qttSell, xV FLOAT DEFAULT 0;
DECLARE previousQttSell, previousXV FLOAT DEFAULT NULL;
DECLARE sPrice, xAvgV, mRAvgV, xUclV, xLclV, mRUclV FLOAT DEFAULT 0;
DECLARE sDt DATE DEFAULT 0;

DECLARE crQttSell CURSOR FOR
    SELECT ts.`sellQuantity`
    FROM tb_sell ts
    WHERE ts.`productID` = 1;

/**Truncating temp tables*/
TRUNCATE tb_metrics;
TRUNCATE `tb_mr_temp_table`;

OPEN crQttSell;
get_QttSell: LOOP
    FETCH crQttSell INTO qttSell;
    INSERT INTO tb_metrics VALUES('', 4, 4, 4, 4, 4, 4, 4, 4, 555);
    SET previousQttSell = qttSell;
END LOOP get_QttSell;
CLOSE crQttSell;

INSERT INTO tb_metrics VALUES('', 6, 6, 6, 6, 6, 6, 6, 6, 7);

END

最佳答案

您必须在fetch语句后检测EOF条件，以避免无限循环：

  DECLARE done INT DEFAULT FALSE;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
  ....
  ....
  get_QttSell: LOOP
    FETCH crQttSell INTO qttSell;
    IF done THEN
      LEAVE get_QttSell;
    END IF;
    INSERT INTO tb_metrics VALUES('', 4, 4, 4, 4, 4, 4, 4, 4, 555);
    SET previousQttSell = qttSell;
  END LOOP get_QttSell;
  ....

阅读文档以获取详细信息：http://dev.mysql.com/doc/refman/5.6/en/cursors.html

关于mysql - 在LOOP语句后插入和更新命令，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/20315738/

loop

mysql - 在LOOP语句后插入和更新命令