The WHILE loop is a loop statement that executes a block of code repeatedly as long as a condition is true.
WHILE expression DO
statements
END WHILE
----------------
delimiter $$
create procedure whilevlr()
begin
declare count int default 0;
while count < 10 do
set count := count + 1;
select count;
end while;
end$$
delimiter ;
delimiter $$
create procedure whilevlr1()
begin
declare count int default 0;
while count < 10 do
set count := count+1;
select count;
end while;
end$$
delimiter;
delimiter $$
create procedure whilevlr3()
begin
declare evennum int default 0;
declare res varchar(100) default "";
while evennum < 50 do
set evennum := evennum+2;
set res := concat( res," - ", evennum);
end while;
select res;
end$$
delimiter ;
call whilevlr3()
----------
drop procedure whilevlr;
delimiter $$
create procedure whilevlr()
begin
declare count int default 0;
while count < 10 do
set count := count + 1;
-- select count;
end while;
select count;
end$$
delimiter ;
------------------
Enter password: *******
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 13
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> delimiter $$
mysql> create procedure whilevlr3()
-> begin
-> declare evennum int default 0;
-> while evennum < 50 do
-> set evennum := evennum+2;
-> select count;
-> end while;
-> end$$
Query OK, 0 rows affected (0.22 sec)
mysql> delimiter;
-> ;
-> $$
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 'delimiter' at line 1
mysql> delimeter ;
-> delimiter ;
-> delimiter $
-> delimiter $$
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 'delimeter ;
delimiter ;delimiter $delimiter' at line 1
mysql> delimiter $$
mysql> create procedure whilevlr3()
-> begin
-> declare evennum int default 0;
-> while evennum < 50 do
-> set evennum := evennum+2;
-> select count;
-> end while;
-> end$$
ERROR 1304 (42000): PROCEDURE whilevlr3 already exists
mysql>
mysql> delimiter ;
mysql> drop procedure whilevlr3;
Query OK, 0 rows affected (0.21 sec)
mysql> delimiter $$
mysql> create procedure whilevlr3()
-> begin
-> declare evennum int default 0;
-> while evennum < 50 do
-> set evennum := evennum+2;
-> select count;
-> end while;
-> end$$
Query OK, 0 rows affected (0.23 sec)
mysql>
mysql> delimiter ;
mysql> call whilevlr3();
ERROR 1054 (42S22): Unknown column 'count' in 'field list'
mysql> drop procedure whilevlr3;
Query OK, 0 rows affected (0.15 sec)
mysql> delimiter $$
mysql> create procedure whilevlr3()
-> begin
-> declare evennum int default 0;
-> while evennum < 50 do
-> set evennum := evennum+2;
-> select evennum;
-> end while;
-> end$$
Query OK, 0 rows affected (0.13 sec)
mysql>
mysql> delimiter ;
mysql> call whilevlr3();
+---------+
| evennum |
+---------+
| 2 |
+---------+
1 row in set (0.00 sec)
+---------+
| evennum |
+---------+
| 4 |
+---------+
1 row in set (0.00 sec)
+---------+
| evennum |
+---------+
| 6 |
+---------+
1 row in set (0.01 sec)
+---------+
| evennum |
+---------+
| 8 |
+---------+
1 row in set (0.01 sec)
+---------+
| evennum |
+---------+
| 10 |
+---------+
1 row in set (0.03 sec)
+---------+
| evennum |
+---------+
| 12 |
+---------+
1 row in set (0.04 sec)
+---------+
| evennum |
+---------+
| 14 |
+---------+
1 row in set (0.04 sec)
+---------+
| evennum |
+---------+
| 16 |
+---------+
1 row in set (0.05 sec)
+---------+
| evennum |
+---------+
| 18 |
+---------+
1 row in set (0.06 sec)
+---------+
| evennum |
+---------+
| 20 |
+---------+
1 row in set (0.06 sec)
+---------+
| evennum |
+---------+
| 22 |
+---------+
1 row in set (0.07 sec)
+---------+
| evennum |
+---------+
| 24 |
+---------+
1 row in set (0.08 sec)
+---------+
| evennum |
+---------+
| 26 |
+---------+
1 row in set (0.09 sec)
+---------+
| evennum |
+---------+
| 28 |
+---------+
1 row in set (0.10 sec)
+---------+
| evennum |
+---------+
| 30 |
+---------+
1 row in set (0.10 sec)
+---------+
| evennum |
+---------+
| 32 |
+---------+
1 row in set (0.11 sec)
+---------+
| evennum |
+---------+
| 34 |
+---------+
1 row in set (0.12 sec)
+---------+
| evennum |
+---------+
| 36 |
+---------+
1 row in set (0.13 sec)
+---------+
| evennum |
+---------+
| 38 |
+---------+
1 row in set (0.14 sec)
+---------+
| evennum |
+---------+
| 40 |
+---------+
1 row in set (0.15 sec)
+---------+
| evennum |
+---------+
| 42 |
+---------+
1 row in set (0.15 sec)
+---------+
| evennum |
+---------+
| 44 |
+---------+
1 row in set (0.16 sec)
+---------+
| evennum |
+---------+
| 46 |
+---------+
1 row in set (0.17 sec)
+---------+
| evennum |
+---------+
| 48 |
+---------+
1 row in set (0.18 sec)
+---------+
| evennum |
+---------+
| 50 |
+---------+
1 row in set (0.18 sec)
Query OK, 0 rows affected (0.20 sec)
mysql> drop wilevlr3;
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 'wilevlr3' at line 1
mysql> drop whilevlr3;
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 'whilevlr3' at line 1
mysql> drop whilevlr3();
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 'whilevlr3()' at line 1
mysql> drop whilevlr3;
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 'whilevlr3' at line 1
mysql> drop procedure whilevlr3;
Query OK, 0 rows affected (0.19 sec)
mysql> delimiter $$
mysql> create procedure whilevlr3()
-> begin
-> declare evennum int default 0;
-> declare res varchar(100) default "";
-> while evennum < 50 do
-> set evennum := evennum+2;
-> set res := concat( res,evennum)
-> end while;
-> select res;
-> 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 'end while;
select res;
end' at line 8
mysql>
mysql> delimiter ;
mysql> delimiter $$
mysql> create procedure whilevlr3()
-> begin
-> declare evennum int default 0;
-> declare res varchar(100) default "";
-> while evennum < 50 do
-> set evennum := evennum+2;
-> set res := concat( res,evennum);
-> end while;
-> select res;
-> end$$
Query OK, 0 rows affected (0.14 sec)
mysql>
mysql> delimiter ;
mysql> call whilevlr3();
+------------------------------------------------+
| res |
+------------------------------------------------+
| 2468101214161820222426283032343638404244464850 |
+------------------------------------------------+
1 row in set (0.03 sec)
Query OK, 0 rows affected (0.03 sec)
mysql> drop procedure whilevlr3;
Query OK, 0 rows affected (0.24 sec)
mysql> delimiter $$
mysql> create procedure whilevlr3()
-> begin
-> declare evennum int default 0;
-> declare res varchar(100) default "";
-> while evennum < 50 do
-> set evennum := evennum+2;
-> set res := concat( res,",",evennum);
-> end while;
-> select res;
-> end$$
Query OK, 0 rows affected (0.33 sec)
mysql>
mysql> delimiter ;
mysql> call whilevlr3();
+-------------------------------------------------------------------------+
| res |
+-------------------------------------------------------------------------+
| ,2,4,6,8,10,12,14,16,18,20,22,24,26,28,30,32,34,36,38,40,42,44,46,48,50 |
+-------------------------------------------------------------------------+
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.