mySQL LOOP Statements

MySQL WHILE Loop

mysql provides loop statement that allow you to execute a block of sql code repeatedly based on a condition.there are three loop statements in mysql :while, repeat and loop.

the while loop is called as pre-test loop.

syntax of while loop:

                     while        expression     do

                                     statements
                                        
                     end  while

MySQL - while loops checks the expression at the beginning of each iteration.if the expression evaluates to true,mysql will executes statements between while and end while until the expression evaluates to false.


MySQL REPEAT loop

the repeat loop statement is known as post-test loop.

syntax of repeat loop :

                     repeat        statements;

                     until      expression
                          
                     end   repeat

in this loop the all statement executes first,and then it evaluates the expression.if the expression evaluates to true, mysql executes the statements repeatedly until the expression evaluates to false.

MySQL WHILE Loop example

               delimiter $$
               
               create  procedure   whileloop()
               
                       begin
                  
                                   declare    x        int;
                                        
                                   declare    str      varchar(100);
                                        
                                   set x = 1;
                                        
                                   set str = '' ;
                                         
                  while    x <= 10    do
                                        
                                             set   str  =  concat(str,x,',');
                                                             
                                             set   x    =  x + 1;
                                                             
                  end while;
                                        
                  select  str;
                                        
                  end$$
                                     
                  delimiter;

REPEAT Loop example

               delimiter $$
               
               create  procedure   whileloop()
               
                       begin
                  
                                   declare    x        int;
                                        
                                   declare    str      varchar(100);
                                        
                                   set x = 1;
                                        
                                   set str = '' ;
                                         
                  repeat   
                                        
                                             set   str  =  concat(str,x,',');
                                                             
                                             set   x    =  x + 1;
                                                             
                  until  x >=10
                  
                  end repeat;
                                        
                  select  str;
                                        
                  end$$
                                     
                  delimiter;

note: the until evaluate expression not end with semicolon(;).