For loop example in MySQL

Question

In MySQL, I have this stored procedure with a LOOP:

DELIMITER $$  
CREATE PROCEDURE ABC()

   BEGIN
      DECLARE a INT Default 0 ;
      simple_loop: LOOP
         SET a=a+1;
         select a;
         IF a=5 THEN
            LEAVE simple_loop;
         END IF;
   END LOOP simple_loop;
END $$

It always prints 1. What is the correct syntax for a MySQL Loop?

This creates store procedure names ABC. What is your data format (one you want to load)?. — Zimbabao, Feb 26 '11 at 04:41
i do not want any data format, i just want to print a value from 1 to 5 — Chitresh, Feb 26 '11 at 04:47
I just tried this code and it worked for me. I had to add CALL ABC(); but it worked. I also added DROP PROCEDURE ABC() at the end because I was only running it as a test. — Alan Stewart, May 07 '20 at 20:06

score 166 · Accepted Answer · edited Dec 25 '11 at 05:26

166

drop table if exists foo;
create table foo
(
id int unsigned not null auto_increment primary key,
val smallint unsigned not null default 0
)
engine=innodb;

drop procedure if exists load_foo_test_data;

delimiter #
create procedure load_foo_test_data()
begin

declare v_max int unsigned default 1000;
declare v_counter int unsigned default 0;

  truncate table foo;
  start transaction;
  while v_counter < v_max do
    insert into foo (val) values ( floor(0 + (rand() * 65535)) );
    set v_counter=v_counter+1;
  end while;
  commit;
end #

delimiter ;

call load_foo_test_data();

select * from foo order by id;

edited Dec 25 '11 at 05:26

Anthony Pegram

123,721
27
225
246

answered Feb 26 '11 at 11:36

Jon Black

16,223
5
43
42

Thank you for your answer, it helped me. Could you be so kind to also see my question http://stackoverflow.com/questions/12259675/start-transaction-inside-begin-end-context-or-outside-and-loop-syntax. My main question is, is it necessary to use `BEGIN...END` and create a procedure if I only want to use `LOOP`? – Green Sep 04 '12 at 09:46
2

**If you're having problems with delimiters,** read http://stackoverflow.com/a/10259528/632951 – Pacerier Mar 10 '15 at 15:51
1

@Ktaria - the key point of the loop example (however implemented) is the placement of the start transaction and commit - why is that ? – Jon Black Jan 31 '21 at 15:23

Eric Leschinski · Answer 2 · 2017-09-09T01:04:15.547

While loop syntax example in MySQL:

delimiter //

CREATE procedure yourdatabase.while_example()
wholeblock:BEGIN
  declare str VARCHAR(255) default '';
  declare x INT default 0;
  SET x = 1;

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

  select str;
END//

Which prints:

mysql> call while_example();
+------------+
| str        |
+------------+
| 1,2,3,4,5, |
+------------+

REPEAT loop syntax example in MySQL:

delimiter //

CREATE procedure yourdb.repeat_loop_example()
wholeblock:BEGIN
  DECLARE x INT;
  DECLARE str VARCHAR(255);
  SET x = 5;
  SET str = '';

  REPEAT
    SET str = CONCAT(str,x,',');
    SET x = x - 1;
    UNTIL x <= 0
  END REPEAT;

  SELECT str;
END//

Which prints:

mysql> call repeat_loop_example();
+------------+
| str        |
+------------+
| 5,4,3,2,1, |
+------------+

FOR loop syntax example in MySQL:

delimiter //

CREATE procedure yourdatabase.for_loop_example()
wholeblock:BEGIN
  DECLARE x INT;
  DECLARE str VARCHAR(255);
  SET x = -5;
  SET str = '';

  loop_label: LOOP
    IF x > 0 THEN
      LEAVE loop_label;
    END IF;
    SET str = CONCAT(str,x,',');
    SET x = x + 1;
    ITERATE loop_label;
  END LOOP;

  SELECT str;

END//

Which prints:

mysql> call for_loop_example();
+-------------------+
| str               |
+-------------------+
| -5,-4,-3,-2,-1,0, |
+-------------------+
1 row in set (0.00 sec)

Do the tutorial: http://www.mysqltutorial.org/stored-procedures-loop.aspx

If I catch you pushing this kind of MySQL for-loop constructs into production, I'm going to shoot you with the foam missile launcher. You can use a pipe wrench to bang in a nail, but doing so makes you look silly.

Following the code you've given, typing `declare str VARCHAR(255) default '';` in the MySQL (5.6) console gives me the error message `Error 1064 (42000): ... for the right syntax to use near '' at line 3`, which is as clear as mud (although I assume it doesn't like the DEFAULT clause). — Agi Hammerthief, May 26 '15 at 12:54
It used to work in prior versions. I think it's the end of line delimiter to change: use the command `delimiter //` before running this. — Eric Leschinski, Dec 07 '16 at 14:57
Can we use Iterate and Leave within While also? These are acting as continue and break. Is Iterate works similar to continue in java? — Deepak, Jan 09 '18 at 06:39

score 17 · Answer 3 · answered Oct 11 '12 at 11:58

Assume you have one table with name 'table1'. It contain one column 'col1' with varchar type. Query to crate table is give below

CREATE TABLE `table1` (
    `col1` VARCHAR(50) NULL DEFAULT NULL
)

Now if you want to insert number from 1 to 50 in that table then use following stored procedure

DELIMITER $$  
CREATE PROCEDURE ABC()

   BEGIN
      DECLARE a INT Default 1 ;
      simple_loop: LOOP         
         insert into table1 values(a);
         SET a=a+1;
         IF a=51 THEN
            LEAVE simple_loop;
         END IF;
   END LOOP simple_loop;
END $$

To call that stored procedure use

CALL `ABC`()

(Thanks F.) Note that "DELIMITER ;" is required after "END $$" — ozma, Aug 01 '22 at 11:31

Bruno Henrique · Answer 4 · 2019-03-01T18:40:43.513

1

You can exchange this local variable for a global, it would be easier.

DROP PROCEDURE IF EXISTS ABC;
DELIMITER $$  
CREATE PROCEDURE ABC()

   BEGIN
      SET @a = 0;
      simple_loop: LOOP
         SET @a=@a+1;
         select @a;
         IF @a=5 THEN
            LEAVE simple_loop;
         END IF;
   END LOOP simple_loop;
END $$

edited Mar 01 '19 at 18:40

answered Mar 01 '19 at 18:34

Bruno Henrique

29
4

For loop example in MySQL

4 Answers4

While loop syntax example in MySQL:

REPEAT loop syntax example in MySQL:

FOR loop syntax example in MySQL:

Linked