mysql - MySQL过程循环错误

当运行查询时，我会得到：
错误：

#1064 - You have an error in your SQL syntax; check the manual
that corresponds to your MySQL server version for the right syntax to use near '' at line 3

SQL语言：

DROP PROCEDURE IF EXISTS insertPromos;
CREATE PROCEDURE insertPromos()
BEGIN
  DECLARE int_val INT DEFAULT 0;
  insertPromosLoop : LOOP

  IF (int_val = 501) THEN
    LEAVE insertPromosLoop;
  END IF;
    INSERT INTO `promo_code` (`code`, `valid_from` ,`valid_to` , `free_period`)VALUES (CONCAT('PROMO', int_val),  '2013-10-10',  '2013-11-10',  'P1M');
    SET int_val = int_val +1;
  END LOOP;
END;
CALL insertPromos();

最佳答案

您需要定义一个新的分隔符

delimiter |

DROP PROCEDURE IF EXISTS insertPromos |

CREATE PROCEDURE insertPromos()
BEGIN
  DECLARE int_val INT DEFAULT 0;
  insertPromosLoop : LOOP

  IF (int_val = 501) THEN
    LEAVE insertPromosLoop;
  END IF;
    INSERT INTO `promo_code` (`code`, `valid_from` ,`valid_to` , `free_period`)VALUES (CONCAT('PROMO', int_val),  '2013-10-10',  '2013-11-10',  'P1M');
    SET int_val = int_val +1;
  END LOOP;
END
|

delimiter ;

CALL insertPromos();

否则，过程定义将在第一个;处结束，这是不正确的。