(https://www.databasejournal.com/features/mysql/mysql-cursors-and-loops.html)

=====================
The While Loop
=====================

The loop has the following syntax:

[label:] WHILE expression DO

    statements

END WHILE [label]

Although you don't need to specify a label because the condition is always specific to the current loop, using labels improve readability.  
A little later on, we’ll see how labels are essential to nested loops.

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `while_loop_proc`()
BEGIN
    DECLARE counter  INT;              
    DECLARE str      VARCHAR(255);
    
    SET     counter  = 1;              
    SET     str      = '';  
    
1_to_5_counter: 
    WHILE counter <= 5 DO                          
        SET str = CONCAT(str,counter,',');                          
        SET counter = counter + 1;               
    END WHILE 1_to_5_counter; 
    
    SELECT LEFT(str, LENGTH(str) - 1); -- remove trailing comma
    
END

The above proc prints the numbers 1 to 5:
1,2,3,4,5


====================
The Repeat Loop
====================

Here is the syntax for the REPEAT loop:

[begin_label:] REPEAT 
  statement_list 
UNTIL search_condition 
END REPEAT [end_label]

A REPEAT loop continues until the expression defined in the UNTIL clause evaluates to TRUE.  
It uses a POST_TEST for checking the loop condition, so it is guaranteed to execute at least once.   
The following proc uses a REPEAT loop to build the same comma-delimited one to five string as above:

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `repeat_loop_proc`()
BEGIN

    DECLARE counter  INT;
    DECLARE str      VARCHAR(255); 
    
    SET counter      = 1;              
    SET str          = '';             

1_to_5_counter: 
    REPEAT                          
        SET  str = CONCAT(str, counter  ,',');                          
        SET  counter  = counter  + 1;               
    UNTIL counter > 5              
    END REPEAT 1_to_5_counter;     
    
    SELECT LEFT(str, LENGTH(str) - 1); -- remove trailing comma

END

========================
The Loop Loop (?!)
========================

Unlike the last two iteration constructs, the Loop statement has NO condition.  Sound dangerous?  You betcha! 

[begin_label:] LOOP
    statement_list
END LOOP [end_label]

Rather than define a specific exit condition, the LOOP loop depends on the careful placement of the LEAVE statement 
to terminate iteration.  It's the equivalent to the break statement in programming languages such as PHP, C/C++, and Java.  
LEAVE requires a label that identifies the loop. More on that in a bit...

LEAVE label;

Ideally, the LEAVE statement should be placed between an IF/END IF so that you can test for the desired exit condition.  
In the following example, the loop prints the numbers one to ten at which point, our IF test executes the LEAVE statement:

SET i=1;
 loop_loop: 
 LOOP
     SET i=i+1;
     SELECT i;
     IF i=10 then
         LEAVE loop_loop;
     END IF;
 END LOOP loop_loop;
 
 
The ITERATE statement can also be included in LOOP loops to immediately begin the next loop iteration, without executing 
any of the remaining statements in the loop. ITERATE is like the continue statement in PHP, C/C++, and Java; 
it has pretty much the same syntax as LEAVE:

ITERATE label;
Here's a variation of the previous example that includes the ITERATE statement.  It's a stored procedure that accepts 
an INT parameter as the start number in our n to 10 output.  As long as i is less than ten, the ITERATE loop_loop; 
statement returns to the top of the loop before the LEAVE statement can be executed.  As soon as i hits eleven or more, 
the IF doesn't catch it and the LEAVE statement causes the loop to exit:

CREATE PROCEDURE do_loop(i INT)
   loop_loop: LOOP
     SET i = i + 1;
     IF i <= 10 THEN
       SELECT i;
       ITERATE loop_loop;
     END IF;
     LEAVE loop_loop;
   END LOOP loop_loop;
 END;
 
==================================== 
Exiting Nested Loops
====================================

The inclusion of the label with the LEAVE and ITERATE statements comes in especially 
handy for managing the execution of nested loops.  Here is a two-level nested loop that prints 
the squares of a chess board, where rows are numbered from 1 to 8 and columns from a to h respectively:

DECLARE row,col INT DEFAULT 1;
 row_loop: LOOP
     SET col=1;
     col_loop: LOOP
         SELECT concat(row, char(col + 96)); --'a' starts at ascii 97
         SET col=col+1;
         IF col>8 THEN
             LEAVE col_loop;
         END IF;
     END LOOP col_loop;
     SET row=row+1;
     IF row>8 THEN
         LEAVE row_loop;
     END IF;
 END LOOP row_loop;
 
================================
 Using Cursors
================================

A cursor is a special kind of loop for traversing through an SQL resultset one row at a time. 
That allows us to perform operations on every record on a one-by-one basis. Just like loops, 
cursors are only supported within stored procedures and functions.  

Here’s a stored procedure to give you a taste of cursors and how they are utilized.  It fetches 
employee IDs and names from the employee table and stores them in variables.  In a real stored proc, 
something more would likely be done with them.  For now, just look at how a loop is used to populate 
the @id and @name variables as well as the CONTINUE HANDLER that sets the exit_loop flag:

DELIMITER $$
 DROP PROCEDURE IF EXISTS  cursor_proc $$
 CREATE PROCEDURE cursor_proc()
 BEGIN
 
   DECLARE @id   VARCHAR(10);
   DECLARE @name VARCHAR(255);
   
   -- this flag will be set to true when cursor reaches end of table
   DECLARE exit_loop BOOLEAN;   
   
   -- Declare the cursor
   DECLARE employee_cursor CURSOR FOR
     SELECT id, name FROM employees;
     
   -- set exit_loop flag to true if there are no more rows
   DECLARE CONTINUE HANDLER FOR NOT FOUND SET exit_loop = TRUE;
   
   -- open the cursor
   OPEN employee_cursor;
   
   -- start looping
   employee_loop: 
   LOOP
     -- read the name from next row into the variables 
     FETCH  employee_cursor INTO @id, @name;
     
     -- check if the exit_loop flag has been set by mysql, 
     -- close the cursor and exit the loop if it has.
     IF exit_loop THEN
         CLOSE employee_cursor;
         LEAVE employee_loop;
         
     END IF;
     
   END LOOP employee_loop;
   
 END $$
 DELIMITER ;
 
 
Conclusion:

A cursor should be reserved for those rare instances where you can’t retrieve all of the 
data that you want using individual queries. You’d be surprised how often a well-crafted 
query or two will work! 


===========================================
MySQL Temporary Table
===========================================

( http://www.mysqltutorial.org/mysql-temporary-table/ )

Introduction to MySQL temporary table
In MySQL, a temporary table is a special type of table that allows you to store a temporary 
result set, which you can reuse several times in a single session.

A temporary table is very handy when it is impossible or expensive to query data that 
requires a single SELECT statement with JOIN clauses. In this case, you can use a temporary 
table to store the immediate result and use another query to process it.

CREATE TEMPORARY TABLE top10customers
SELECT p.customerNumber, 
       c.customerName, 
       FORMAT(SUM(p.amount),2) total
FROM payments p
INNER JOIN customers c ON c.customerNumber = p.customerNumber
GROUP BY p.customerNumber
ORDER BY total DESC
LIMIT 10;


Removing a MySQL temporary table
You can use the DROP TABLE statement to remove temporary tables however it is good 
practice to add the TEMPORARY keyword as follows:

DROP TEMPORARY TABLE table_name;