Translate

Monday, 18 October 2021

labled Loops in stored procedure sql videos in telugu 99

 https://youtu.be/cOi_TNMtO2w

----------------------------------------------------
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.