MySQL - REPEAT Statement

Stored procedures are sub routines, segment of SQL statements which are stored in SQL catalog. These procedures contain IN and OUT parameters, or both. They may return result sets in case you use SELECT statements; they can return multiple result-sets. You can also create functions in MYSQL.

Similar to other programming languages MySQL provides support for the flow control statements such as IF, CASE, ITERATE, LEAVE LOOP, WHILE, and REPEAT. You can use these statements in the stored programs (procedures), and RETURN in stored functions. You can use one Flow Control Statement with in another.

MySQL REPEAT Statement

The REPEAT statement in MySQL is used to repeat the given set of statements (or statement) until the value of the given search condition is TRUE. The statement(s) in the LOOP ends with a semi colon (or. the current delimiter).

Syntax

Following is the syntax of the REPEAT Statement −

begin_label: REPEAT
   statement_list
UNTIL search_condition
END REPEAT end_label

Where, statement_list is a single or set of statements that are to be repeated. begin_label and end_label are the optional labels of the REPEAT statement and, search_condition is the condition whose value should be TRUE for the loop to be terminated.

Example 1

Following query demonstrates the usage of the REPEAT statement with a procedure −

DELIMITER //
CREATE PROCEDURE RepeatExample()
BEGIN
   DECLARE val INT;
   DECLARE squares INT;
   DECLARE res VARCHAR(100);
   SET val=1;
   SET squares=1;
   SET res = '';
   REPEAT
      SET squares = val*val;
      SET res = CONCAT(res, squares,',');
      SET val = val + 1;
   UNTIL val >= 10
   END REPEAT;
   SELECT res;
END//

DELIMITER ;

You can call the above procedure as follows −

CALL RepeatExample; //

Output

Following is the output of the above query −

Example 2

Following query is another example of the REPEAT query −

DELIMITER //
CREATE PROCEDURE test()
   BEGIN
      DECLARE num INT default 1;
      DECLARE res Varchar(50) default '';
      REPEAT
         SET res = CONCAT(res,num,',');
         SET num = num*5;
      UNTIL num > 78125
      END REPEAT;
      SELECT res;
   END //
DELIMITER ;

You can call the above created procedure as shown below −

call test//

Output

The above query produces the following output −