The MySQL LOOP statement could be used to run a block of code or set of statements, again and again, depends on the condition.
Syntax :
[labelname:] LOOP
statements
END LOOP [labelname]
Parameters –
labelname : It is an optional label at the start and end.
statements : They could have one or multiple statements, each ended by a semicolon (;) and executed by LOOP.
https://www.geeksforgeeks.org/loops-in-mysql/
drop procedure loopvlr;
delimiter $$
create procedure loopvlr()
begin
declare count int default 0;
the_loop: loop
if count = 10 then
leave the_loop;
end if;
set count := count + 1;
select count;
end loop;
end$$
delimiter ;
drop procedure loopvlr;
delimiter $$
create procedure loopvlr()
begin
declare count int default 0;
declare nlist varchar(40) default "";
the_loop: loop
if count = 10 then
leave the_loop;
end if;
set nlist := concat(nlist,count);
set count := count + 1;
end loop;
select nlist;
end$$
delimiter ;
------------------
drop procedure loopvlr;
delimiter $$
create procedure loopvlr()
begin
declare count int default 0;
declare nlist varchar(40) default "";
the_loop: loop
if count = 10 then
leave the_loop;
end if;
set nlist := concat(nlist, count);
if count != 9 then
set nlist := concat(nlist,",");
end if;
set count := count + 1;
end loop;
select nlist;
end$$
delimiter ;
----------------
drop procedure loopvlr;
delimiter $$
create procedure loopvlr()
begin
declare count int default 1;
declare nlist varchar(30) default "";
the_loop: loop
if count = 10 then
leave the_loop;
end if;
set nlist := concat(nlist, count);
set nlist := concat(nlist,",");
set count := count + 1;
end loop;
select nlist;
end$$
delimiter ;
--------------------
drop procedure loopvlr;
delimiter $$
create procedure loopvlr()
begin
declare count int default 1;
declare nlist varchar(30) default "";
the_loop: loop
if count = 10 then
leave the_loop;
end if;
set nlist := concat(nlist, count);
if count != 9 then
set nlist := concat(nlist,",");
end if;
set count := count + 1;
end loop;
select nlist;
end$$
delimiter ;
---------------------
delimiter $$
create procedure loopdemo()
begin
declare count int default 0;
declare numberlist varchar(30) default "";
the_loop: loop
if count = 10 then
leave the_loop;
end if;
set numberlist := concat(numberlist, count);
if count != 9 then
set numberlist := concat(numberlist, ", ");
end if;
set count := count + 1;
end loop;
select numberlist;
end$$
delimiter ;
------------------
Enter password: *******
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 8.0.25 MySQL Community Server - GPL
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> use classicmodels;
Database changed
mysql> drop procedure loopvlr;
Query OK, 0 rows affected (0.20 sec)
mysql> delimiter $$
mysql> create procedure loopvlr()
-> begin
-> declare count int default 0:
-> the_loop: loop
-> if count = 10 then
-> leave the_loop;
-> end if;
-> set count := count+1;
-> select count;
-> end loop;
-> end$$
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ':
the_loop: loop
if count = 10 then
leave the_loop;
end if;
set count := count+1' at line 3
mysql> delimiter ;drop procedure loopvlr;
mysql> delimiter $$
mysql> create procedure loopvlr()
-> begin
-> declare count int default 0:
-> the_loop: loop
-> if count = 10 then
-> leave the_loop;
-> end if;
-> set count := count+1;
-> select count;
-> end loop;
-> end$$
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ':
the_loop: loop
if count = 10 then
leave the_loop;
end if;
set count := count+1' at line 3
mysql> delimiter ;
mysql> drop procedure loopvlr;
ERROR 1305 (42000): PROCEDURE classicmodels.loopvlr does not exist
mysql> delimiter $$
mysql> create procedure loopvlr()
-> begin
-> declare count int default 0:
-> the_loop: loop
-> if count = 10 then
-> leave the_loop;
-> end if;
-> set count := count+1;
-> select count;
-> end loop;
-> end$$
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ':
the_loop: loop
if count = 10 then
leave the_loop;
end if;
set count := count+1' at line 3
mysql> delimiter ;
mysql> drop procedure loopvlr;
ERROR 1305 (42000): PROCEDURE classicmodels.loopvlr does not exist
mysql> delimiter $$
mysql> create procedure loopvlr()
-> begin
-> declare count int default 0:
-> the_loop: loop
-> if count = 10 then
-> leave the_loop;
-> end if;
-> set count := count + 1;
-> select count;
-> end loop;
-> end$$
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ':
the_loop: loop
if count = 10 then
leave the_loop;
end if;
set count := count +' at line 3
mysql> delimiter ;
mysql> drop procedure loopvlr;
ERROR 1305 (42000): PROCEDURE classicmodels.loopvlr does not exist
mysql>
mysql> delimiter $$
mysql> create procedure loopvlr()
-> begin
-> declare count int default 0;
-> the_loop: loop
-> if count = 10 then
-> leave the_loop;
-> end if;
-> set count := count + 1;
-> select count;
-> end loop;
-> end$$
Query OK, 0 rows affected (0.14 sec)
mysql>
mysql> delimiter ;
mysql> drop procedure loopvlr;
Query OK, 0 rows affected (0.19 sec)
mysql> delimiter $$
mysql> create procedure loopvlr()
-> begin
-> declare count int default 0:
-> the_loop: loop
-> if count = 10 then
-> leave the_loop;
-> end if;
-> set count := count + 1;
-> select count;
-> end loop;
-> end$$
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ':
the_loop: loop
if count = 10 then
leave the_loop;
end if;
set count := count +' at line 3
mysql> delimiter ;
mysql>
mysql> drop procedure loopvlr;
ERROR 1305 (42000): PROCEDURE classicmodels.loopvlr does not exist
mysql> delimiter $$
mysql> create procedure loopvlr()
-> begin
-> declare count int default 0:
-> the_loop: loop
-> if count = 10 then
-> leave the_loop;
-> end if;
-> set count := count + 1;
-> select count;
-> end loop;
-> end$$
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ':
the_loop: loop
if count = 10 then
leave the_loop;
end if;
set count := count +' at line 3
mysql>
mysql> delimiter ;
mysql> drop procedure loopvlr;
ERROR 1305 (42000): PROCEDURE classicmodels.loopvlr does not exist
mysql> drop procedure loopvlr;
ERROR 1305 (42000): PROCEDURE classicmodels.loopvlr does not exist
mysql>
mysql> delimiter $$
mysql> create procedure loopvlr()
-> begin
-> declare count int default 0:
-> the_loop: loop
-> if count = 10 then
-> leave the_loop;
-> end if;
-> set count := count + 1;
-> select count;
-> end loop;
-> end$$
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ':
the_loop: loop
if count = 10 then
leave the_loop;
end if;
set count := count +' at line 3
mysql>
mysql> delimiter ;
mysql>
mysql> drop procedure loopvlr;
ERROR 1305 (42000): PROCEDURE classicmodels.loopvlr does not exist
mysql>
mysql> delimiter $$
mysql> create procedure loopvlr()
-> begin
-> declare count int default 0;
-> the_loop: loop
-> if count = 10 then
-> leave the_loop;
-> end if;
-> set count := count + 1;
-> select count;
-> end loop;
-> end$$
Query OK, 0 rows affected (0.14 sec)
mysql>
mysql> delimiter ;
mysql> call loopvlr();
+-------+
| count |
+-------+
| 1 |
+-------+
1 row in set (0.02 sec)
+-------+
| count |
+-------+
| 2 |
+-------+
1 row in set (0.02 sec)
+-------+
| count |
+-------+
| 3 |
+-------+
1 row in set (0.03 sec)
+-------+
| count |
+-------+
| 4 |
+-------+
1 row in set (0.04 sec)
+-------+
| count |
+-------+
| 5 |
+-------+
1 row in set (0.05 sec)
+-------+
| count |
+-------+
| 6 |
+-------+
1 row in set (0.06 sec)
+-------+
| count |
+-------+
| 7 |
+-------+
1 row in set (0.07 sec)
+-------+
| count |
+-------+
| 8 |
+-------+
1 row in set (0.08 sec)
+-------+
| count |
+-------+
| 9 |
+-------+
1 row in set (0.09 sec)
+-------+
| count |
+-------+
| 10 |
+-------+
1 row in set (0.10 sec)
Query OK, 0 rows affected (0.11 sec)
mysql> drop procedure loopvlr;
Query OK, 0 rows affected (0.16 sec)
mysql>
mysql> delimiter $$
mysql> create procedure loopvlr()
-> begin
-> declare count int default 0;
-> declare nlist varchar(40) default "";
-> the_loop: loop
-> if count = 10 then
-> leave the_loop;
-> end if;
-> set nlist := concat(nlist, count);
->
-> set count := count + 1;
->
-> end loop;
-> select nlist;
-> end$$
Query OK, 0 rows affected (0.10 sec)
mysql>
mysql> delimiter ;
mysql> call vlrinst();
ERROR 1305 (42000): PROCEDURE classicmodels.vlrinst does not exist
mysql> call loopvlr();
+------------+
| nlist |
+------------+
| 0123456789 |
+------------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.01 sec)
mysql> drop procedure loopvlr;
Query OK, 0 rows affected (0.10 sec)
mysql>
mysql> delimiter $$
mysql> create procedure loopvlr()
-> begin
-> declare count int default 0;
-> declare nlist varchar(40) default "";
-> the_loop: loop
-> if count = 10 then
-> leave the_loop;
-> end if;
-> set count := count + 1;
-> set nlist := concat(nlist,count);
->
-> end loop;
-> select nlist;
-> end$$
Query OK, 0 rows affected (0.25 sec)
mysql>
mysql> delimiter ;
mysql> call loopvlr();
+-------------+
| nlist |
+-------------+
| 12345678910 |
+-------------+
1 row in set (0.03 sec)
Query OK, 0 rows affected (0.03 sec)
mysql> drop procedure loopvlr;
Query OK, 0 rows affected (0.18 sec)
mysql>
mysql> delimiter $$
mysql> create procedure loopvlr()
-> begin
-> declare count int default 0;
-> declare nlist varchar(40) default "";
-> the_loop: loop
-> if count = 10 then
-> leave the_loop;
-> end if;
-> set count := count + 1;
-> set nlist := concat(count,nlist);
->
-> end loop;
-> select nlist;
-> end$$
Query OK, 0 rows affected (0.12 sec)
mysql>
mysql> delimiter ;
mysql> call loopvlr();
+-------------+
| nlist |
+-------------+
| 10987654321 |
+-------------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.01 sec)
mysql> drop procedure loopvlr;
Query OK, 0 rows affected (0.12 sec)
mysql>
mysql> delimiter $$
mysql> create procedure loopvlr()
-> begin
-> declare count int default 0;
-> declare nlist varchar(40) default "";
-> the_loop: loop
-> if count = 10 then
-> leave the_loop;
-> end if;
-> set nlist := concat(count,nlist);
-> set count := count + 1;
->
->
-> end loop;
-> select nlist;
-> end$$
Query OK, 0 rows affected (0.11 sec)
mysql>
mysql> delimiter ;
mysql> call loopvlr();
+------------+
| nlist |
+------------+
| 9876543210 |
+------------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.01 sec)
mysql> drop procedure loopvlr;
Query OK, 0 rows affected (0.17 sec)
mysql>
mysql> delimiter $$
mysql> create procedure loopvlr()
-> begin
-> declare count int default 0;
-> declare nlist varchar(40) default "";
-> the_loop: loop
-> if count = 10 then
-> leave the_loop;
-> end if;
-> set nlist := concat(nlist, count);
-> set nlist := concat(nlist,",")
-> set count := count + 1;
->
-> end loop;
-> select nlist;
-> end$$
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'set count := count + 1;
end loop;
select nlist;
end' at line 11
mysql>
mysql> delimiter ;
mysql> drop procedure loopvlr;
ERROR 1305 (42000): PROCEDURE classicmodels.loopvlr does not exist
mysql>
mysql> delimiter $$
mysql> create procedure loopvlr()
-> begin
-> declare count int default 0;
-> declare nlist varchar(40) default "";
-> the_loop: loop
-> if count = 10 then
-> leave the_loop;
-> end if;
-> set nlist := concat(nlist, count);
-> set nlist := concat(nlist,",");
-> set count := count + 1;
->
-> end loop;
-> select nlist;
-> end$$
Query OK, 0 rows affected (0.13 sec)
mysql>
mysql> delimiter ;
mysql> call loopvlr();
+----------------------+
| nlist |
+----------------------+
| 0,1,2,3,4,5,6,7,8,9, |
+----------------------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.01 sec)
mysql> drop procedure loopvlr;
Query OK, 0 rows affected (0.22 sec)
mysql>
mysql> delimiter $$
mysql> create procedure loopvlr()
-> begin
-> declare count int default 0;
-> declare nlist varchar(40) default "";
-> the_loop: loop
-> if count = 10 then
-> leave the_loop;
-> end if;
-> set nlist := concat(nlist, count);
-> if count != 9
-> set nlist := concat(nlist,",");
-> end if;
->
-> set count := count + 1;
->
-> end loop;
-> select nlist;
-> end$$
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'set nlist := concat(nlist,",");
end if;
set count := count + 1;
end loop;
sele' at line 11
mysql>
mysql> delimiter ;
mysql> drop procedure loopvlr;
ERROR 1305 (42000): PROCEDURE classicmodels.loopvlr does not exist
mysql>
mysql> delimiter $$
mysql> create procedure loopvlr()
-> begin
-> declare count int default 0;
-> declare nlist varchar(40) default "";
-> the_loop: loop
-> if count = 10 then
-> leave the_loop;
-> end if;
-> set nlist := concat(nlist, count);
-> if count != 9 then
-> set nlist := concat(nlist,",");
-> end if;
->
-> set count := count + 1;
->
-> end loop;
-> select nlist;
-> end$$
Query OK, 0 rows affected (0.11 sec)
mysql>
mysql> delimiter ;
mysql> call loopvlr();
+---------------------+
| nlist |
+---------------------+
| 0,1,2,3,4,5,6,7,8,9 |
+---------------------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.01 sec)
mysql>
https://www.mysqltutorial.org/mysql-stored-procedure/mysql-while-loop/
No comments:
Post a Comment
Note: only a member of this blog may post a comment.