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
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.
解决方案
I think this is what you need:
DELIMITER $$
CREATE PROCEDURE dowhile()
BEGIN
DECLARE v1 INT DEFAULT 5;
WHILE v1 > 0 DO
SET v1 = v1 - 1;
END WHILE;
END;
DELIMITER ;
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.