Intellipaat Back

Explore Courses Blog Tutorials Interview Questions
0 votes
2 views
in SQL by (6.1k points)

In MySQL, I have this following stored procedure along with a For loop in it:

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 is always printing "1". What is the correct syntax for the for loop in MySQL?

1 Answer

0 votes
by (12.7k points)

Check-out the below code:

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;

Want to get certified in SQL? Register for this perfect SQL Training course by Intellipaat.

Related questions

0 votes
1 answer
asked Mar 25, 2021 in Java by dante07 (13.1k points)
0 votes
1 answer
asked Feb 19, 2021 in Python by laddulakshana (16.4k points)
0 votes
1 answer

31k questions

32.8k answers

501 comments

693 users

Browse Categories

...