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)