loops

Loop statements provide additional procedural logic for your reports. However, use them judiciously to avoid accidentally serializing database operations, which can significantly degrade performance.

WHILE Statement

The WHILE loop continues to execute a block of code as long as a specified condition remains true.

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 Statement

The REPEAT loop executes a block of code at least once and continues until a specific termination condition is met.

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 Statement

The LOOP statement defines an unconditional loop that must be explicitly terminated using the LEAVE command.

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)