Skip navigation links
12.6.6 Cursors »
Section Navigation      [Toggle]

12.6.5.8. WHILE Syntax

[begin_label:] WHILE search_condition DO
    statement_list
END WHILE [end_label]

The statement list within a WHILE statement is repeated as long as the search_condition expression is true. statement_list consists of one or more SQL statements, each terminated by a semicolon (;) statement delimiter.

A WHILE statement can be labeled. For the rules regarding label use, see Section 12.6.2, “Statement Label Syntax”.

Example: 

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

  WHILE v1 > 0 DO
    ...
    SET v1 = v1 - 1;
  END WHILE;
END;
Copyright © 1997, 2012, Oracle and/or its affiliates. All rights reserved. Legal Notices

User Comments

Posted by Neil Davis on January 16 2008 4:04am[Delete] [Edit]

If the above example doesn't work for you,
wrap the statements within the while loop with BEGIN/END.
WHILE [condition] DO
BEGIN
..code...
END;
END WHILE;

Had to do this on windows to get mysql to add function. Still testing...

-neil

Posted by Mike Jstam on April 9 2011 8:48am[Delete] [Edit]

(tested on MySQL Community server 5.0.51)

The first example below returns the expected
-- -----------------------------------------
-- Hello Hello Hello Hello Hello Hello\r\n
-- Hello Hello Hello Hello Hello Hello\r\n
-- Hello Hello Hello Hello Hello Hello\r\n
-- Hello Hello Hello Hello Hello Hello\r\n
-- Hello Hello Hello Hello Hello Hello\r\n
-- -----------------------------------------

The second example below returns
-- -----------------------------------------
-- Hello Hello Hello Hello Hello Hello\r\n
-- \r\n
-- \r\n
-- \r\n
-- \r\n
-- -----------------------------------------

# -- The following code works just fine
# -- Keeping it in two variables @secho1 and @secho2
# -- ending it with a concatenation after looping

DROP PROCEDURE IF EXISTS dowhiletest;
DELIMITER //
CREATE PROCEDURE dowhiletest()
BEGIN

DECLARE v1 INT DEFAULT 5;
DECLARE v2 INT DEFAULT 6;

SET @secho1 = '';
SET @secho2 = '';

v1loop: WHILE v1 > 0 DO

v2loop: WHILE v2 > 0 DO
SELECT CONCAT(@secho2,' Hello') INTO @secho2;
SET v2 = v2 - 1;
END WHILE v2loop;

SELECT CONCAT(@secho1,@secho2,'\r\n') INTO @secho1;
SET v1 = v1 - 1;
END WHILE v1loop;

SELECT @secho1 AS `val1`, @secho2 AS `val2`;

END//
DELIMITER ;

# -- The following code does something obscure and
# -- clears the variable in a sense without
# -- ending it with a concatenation after looping

# -- This means it drops the inner WHILE statement
# -- rendering addition of blank lines as if v2
# -- did not exist after first run

DROP PROCEDURE IF EXISTS dowhiletest;
DELIMITER //
CREATE PROCEDURE dowhiletest()
BEGIN

DECLARE v1 INT DEFAULT 5;
DECLARE v2 INT DEFAULT 6;

SET @secho1 = '';
# -- Note! Only one variable for all storage
v1loop: WHILE v1 > 0 DO

v2loop: WHILE v2 > 0 DO
SELECT CONCAT(@secho1,' Hello') INTO @secho1;
SET v2 = v2 - 1;
END WHILE v2loop;

SELECT CONCAT(@secho1,'\r\n') INTO @secho1;
SET v1 = v1 - 1;
END WHILE v1loop;

SELECT @secho1 AS `val1`;

END//
DELIMITER ;

Add your own comment.