在MySQL查询中使用WHILE LOOP时出错 [英] Error using WHILE LOOP in MySQL query

问题描述

我正在尝试在MySQL(MariaDB 5.5.39)中运行SQL查询

I'm trying to run a SQL query in MySQL (MariaDB 5.5.39)

CREATE PROCEDURE dowhile()
BEGIN
  DECLARE v1 INT DEFAULT 5;

  WHILE v1 > 0 DO
    SET v1 = v1 - 1;
  END WHILE;
END;

但是当我运行该查询时，出现以下错误

But when I run that query I get the following error(s)

MariaDB [elis27rel]> source /home/vagrant/test.sql;
--------------
CREATE PROCEDURE dowhile()
BEGIN
  DECLARE v1 INT DEFAULT 5
--------------

ERROR 1064 (42000) at line 1 in file: '/home/vagrant/test.sql': You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near
'' at line 3
--------------
WHILE v1 > 0 DO
    SET v1 = v1 - 1
--------------

ERROR 1064 (42000) at line 5 in file: '/home/vagrant/test.sql': You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near
'WHILE v1 > 0 DO
    SET v1 = v1 - 1' at line 1
--------------
END WHILE
--------------

ERROR 1064 (42000) at line 7 in file: '/home/vagrant/test.sql': You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near
'END WHILE' at line 1
--------------
END
--------------

ERROR 1064 (42000) at line 8 in file: '/home/vagrant/test.sql': You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near
'END' at line 1

我不知道我的代码有什么问题.我从mysql网站 http://dev.mysql复制并粘贴了该代码.com/doc/refman/5.0/en/while.html ，并希望使用该脚本来弄湿我的脚.任何帮助将不胜感激.

I don't know what the issues with my code are. I copied and pasted that code from the mysql website http://dev.mysql.com/doc/refman/5.0/en/while.html and was hoping to use that script to get my feet wet. Any help would be appreciated.

推荐答案

我认为这是您需要的:

DELIMITER $$

CREATE PROCEDURE dowhile()
BEGIN
  DECLARE v1 INT DEFAULT 5;

  WHILE v1 > 0 DO
    SET v1 = v1 - 1;
  END WHILE;
END;

DELIMITER ;

定界符告诉MySQL不要在分号处结束该语句-这将是一个问题，因为存储过程定义未在第一个分号处完成.

The delimiter tells MySQL not to end the statement at the semi-colon -- which would be a problem because the stored procedure definition isn't finished at the first semicolon.

这篇关于在MySQL查询中使用WHILE LOOP时出错的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！