While Loop in MySQL

Yusuf SEZER

5 years ago

The article contains information about the while loop in MySQL and its usage.

While Loop in MySQL

The while loop, which is one of the loops used in programming languages, is used for repetitive operations according to a condition.

WHILE MyCondition DO
-- Commands
END WHILE;

WHILE MyCondition DO

-- Commands

END WHILE;

An example of using a while loop in a stored procedure is as follows.

DELIMITER //
CREATE PROCEDURE while_loop(IN number INT, INOUT result TEXT)
BEGIN

  DECLARE x INT;
  SET x = 1;

  WHILE x <= result DO
  SET result = CONCAT(result, x, ',');
  SET x = x + 1;
  END WHILE;

END //
DELIMITER ;

DELIMITER //

CREATE PROCEDURE while_loop(IN number INT, INOUT result TEXT)

BEGIN

DECLARE x INT;

SET x = 1;

WHILE x <= result DO

SET result = CONCAT(result, x, ',');

SET x = x + 1;

END WHILE;

END //

DELIMITER ;

Let’s run the stored procedure.

SET @result= '';
CALL while_loop(10, @result);
SELECT @result;

SET @result= '';

CALL while_loop(10, @result);

SELECT @result;

When we run the stored procedure, numbers 1 to 10 will be written in a comma separated form.

Have a good days.