How to run this MySQL statement?

Question

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
.......

Answer 1

delimiter |
CREATE PROCEDURE doiterate(p1 INT)
BEGIN
  label1: LOOP
    SET p1 = p1 + 1;
    IF p1 < 10 THEN
      ITERATE label1;
    END IF;
    LEAVE label1;
  END LOOP label1;
END;
|
delimiter ;

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 with delimiter ;