See the MySQL example on http://dev.mysql.com/doc/refman/5.5/en/loop.html:
I copy it and paste on MySQL command line but it stop at every semicolon.
I wonder how to make these sentences work? I really don't know why.
mysql> CREATE PROCEDURE doiterate(p1 INT)
-> BEGIN
-> label1: LOOP
-> SET p1 = p1 + 1;
ERROR 1046 (3D000): No database selected
mysql> IF p1 < 10 THEN
-> ITERATE label1;
ERROR 1064 (42000): 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
'IF p1 < 10 THEN
ITERATE label1' at line 1
mysql> END IF;
ERROR 1064 (42000): 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
'END IF' at line 1
.......
Best Answer
If you don't set another delimiter than
;
the statement will end at the first;
and your procedure definition will be incomplete. You need to tell MySQL that the statement should end at the delimiter you defined. After that you can set the delimiter back withdelimiter ;