Using Loops

With loop statements, you can add additional programmability to your reports. Be aware that you do not accidentally serialize SQL statements when using SQL. This may have a negative impact on performance.

WHILE ... END WHILE

          
          
              
              drop procedure if exists sp_DBR_fibonacci
$$
create procedure sp_DBR_fibonacci( in_value int )
begin
 
declare v_count int;
declare v_text varchar( 500 );
declare v_fn2 int default 0;
declare v_fn1 int default 1;
declare v_fn int;
 
set v_count = 2;
set v_text = concat(v_fn2, " ", v_fn1, " ");
 
while (v_count < in_value) do
    set v_fn = v_fn2 + v_fn1;
    set v_text = concat(v_text, v_fn, " " );    
    set v_fn2 = v_fn1;
    set v_fn1 = v_fn;
    set v_count = v_count + 1;
end while;
 
select v_text as 'fibonacci';
     
end
$$

            

        

call sp_DBR_fibonacci( 12 );
+-------------------------------+
| Fibonacci                     |
+-------------------------------+
| 0 1 1 2 3 5 8 13 21 34 55 89  | 
+-------------------------------+
1 row in set (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

REPEAT .. END REPEAT

          
              drop procedure if exists sp_DBR_hellos
$$
create procedure sp_DBR_hellos()
begin
 
declare v_count int default 0;
declare v_text varchar( 50 ) default "";
 
repeat
    set v_text = concat(v_text, "hello ");
    set v_count = v_count + 1;
until v_count = 5 end repeat;
 
select v_text as 'hellos';
 
end
$$

call sp_DBR_hellos();
+--------------------------------+
| Hellos                         |
+--------------------------------+
| Hello Hello Hello Hello Hello  | 
+--------------------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

LOOP .. END LOOP

          
          
              
              drop procedure if exists sp_DBR_countdown
$$
create procedure sp_DBR_countdown()
begin
 
declare v_count int default 10;
declare v_text varchar( 100 ) default "";
 
my_loop_label: loop
    set v_text = concat(v_text, v_count, " ");
    set v_count = v_count - 1;
    if v_count = 4 then
        set v_text = concat(v_text, "#main engine start# ");    
    elseif v_count = 0 then
        set v_text = concat(v_text, "#solid rocket booster ignition and liftoff#"); 
        leave my_loop_label;
    end if;
 
end loop;
select v_text as 'countdown ...';
 
end
$$

            

        

          
              call sp_DBR_countdown();
+----------------------------------------------------------------------------------------+
| Countdown ...                                                                          |
+----------------------------------------------------------------------------------------+
| 10 9 8 7 6 5 #Main engine start# 4 3 2 1 #Solid rocket booster ignition and liftoff#   | 
+----------------------------------------------------------------------------------------+
1 row in set (0.01 sec)
 
Query OK, 0 rows affected (0.01 sec)