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

Question

我正在尝试在 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

Answer 1

您必须在 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