LOOPING IN MySQL 

LOOP statement used to run a block of code repetitively based on a condition.

 

Introduction to MySQL LOOP statement

The LOOP statement allows to execute one or more statements repeatedly.

syntax:

[begin_label:] LOOP

    statement_list

END LOOP [end_label]

 

The LOOP can have optional labels at the beginning and end of the block.

The LOOP executes the statement_list repeatedly.

The statement_list may have one or more statements, each terminated by a semicolon (;) statement delimiter.

the loop terminates (ends) when a condition is satisfied by using the LEAVE statement.

 

Syntax of the LOOP statement used with LEAVE statement:

[label]: LOOP

    ...

    -- terminate the loop

    IF condition THEN

        LEAVE [label];

    END IF;

    ...

END LOOP;

The LEAVE statement immediately exits the loop.

It works like the break statement (as PHP, C/C++, and Java.).

In addition to the LEAVE statement, also use the ITERATE statement to skip the current loop iteration and start a new iteration.

The ITERATE is similar to the continue statement (PHP, C/C++, and Java.).

 

Example

Creates a stored procedure that uses a LOOP loop statement:

 DELIMITER $$

CREATE PROCEDURE LoopDemo()

BEGIN

                DECLARE x  INT;

                DECLARE str  VARCHAR(255);

                       SET x = 1;

                SET str =  '';

       

                loop_label:  LOOP

                                IF  x > 10 THEN

                                                LEAVE  loop_label;

                                END  IF;

                                SET  x = x + 1;

                                IF  (x mod 2) THEN

                                                ITERATE  loop_label;

                                ELSE

                                                SET  str = CONCAT(str,x,',');

                                END  IF;

                END LOOP;

                SELECT str;

END$$

 

DELIMITER ;

Explanation:-

• The stored procedure constructs a string from the even numbers e.g., 2, 4, and 6.
• The loop_label before the LOOPstatement for using with the ITERATE and LEAVE statements.
• If the value of  x is greater than 10, the loop is terminated due to the LEAVE statement.
• If the value of the x is an odd number, the ITERATE ignores and starts a new loop iteration.
• If the value of the x is an even number, the block in the ELSE statement will build the result string from even numbers.

 

Call LoopDemo stored procedure:

CALL LoopDemo();

 

Output:

+-------------+

| str         |

+-------------+

| 2,4,6,8,10, |

+-------------+

1 row in set (0.01 sec)

======================================================================

 

WHILE Loop in MySQL

Execute one or more statements as code block repeatedly until a condition is true.

Also called pretest loop (checks the search_condition before the statement_list executes).

Syntax:

[begin_label:] WHILE search_condition DO

    statement_list

END WHILE [end_label]

Explanation:

1.      Specify a search condition after the WHILE keyword.

It checks the search_condition at the beginning of each iteration.

If the search_condition is TRUE, the WHILE executes the statement_list as long as the search_condition

is TRUE.

2.      Specify one or more statements that will execute between the DO and END WHILE keywords.

3.      Specify optional labels for the WHILE statement at the beginning and end of the loop construct.

Example

Delimiter //

CREATE PROCEDURE Wl1()

BEGIN

DECLARE A INT;

DECLARE XYZ Varchar(50);

SET A = 1;

SET XYZ = '';

WHILE A <=10 DO

SET XYZ = CONCAT(XYZ,A,',');

SET A = A + 1;

END WHILE;

select xyz;

END //

Compile this code;

Execute this code:-

call Wl1();

 

OUTPUT:-

xyz

1,2,3,4,5,6,7,8,9,10,

============================================================================

MySQL REPEAT Loop

The REPEAT statement executes one or more statements until a search condition is true.

Syntax:

[begin_label:] REPEAT

    statement

UNTIL search_condition

END REPEAT [end_label]

The REPEAT executes the statement until the search_condition evaluates to true.

The REPEAT checks the search_condition after the execution of statement, therefore, the statement always executes at least once. This is why the REPEAT is also known as a post-test loop.

The REPEAT statement can have labels at the beginning and at the end. These labels are optional.

The following flowchart illustrates the REPEAT loop:

REPEAT loop example

creates a stored procedure called RepeatDemo  that uses the REPEAT statement to concatenate numbers from 1 to 9:

 

DELIMITER $$

 

CREATE PROCEDURE RepeatDemo()

BEGIN

    DECLARE counter INT DEFAULT 1;

    DECLARE result VARCHAR(100) DEFAULT '';

   

    REPEAT

        SET result = CONCAT(result,counter,',');

        SET counter = counter + 1;

    UNTIL counter >= 10

    END REPEAT;

   

    -- display result

    SELECT result;

END$$

 

DELIMITER ;

Explanation:-

In this stored procedure:

1.       Declare two variables counter and result and set their initial values to 1 and blank.

The counter variable is used for counting from 1 to 9 in the loop.

And the result variable is storing the concatenated string after each loop iteration.

2.      Append counter value to the result variable using the CONCAT() function until the counter is greater than or equal to 10.

 

Call the RepeatDemo() stored procedure:

CALL RepeatDemo();

 

Output:

+--------------------+

| result             |

+--------------------+

| 1,2,3,4,5,6,7,8,9, |

+--------------------+

1 row in set (0.02 sec)

=========================================================================

 

MySQL LEAVE

The LEAVE statement exits the flow control and terminates a loop or exit from the stored program label.

Syntax:

LEAVE label;

 In this syntax, specify the label of the block that want to exit after
the LEAVE keyword.

If the label is the outermost of the stored procedure  or function block, LEAVE terminates the stored procedure or function.

 

CREATE PROCEDURE sp_name()

sp: BEGIN

    IF condition THEN

        LEAVE sp;

    END IF;

    -- another statement

END$$

Example:-

creates a stored procedure for checking the credit of a given customer in the customers table.

 

Create store procedure CheckCredit()

DELIMITER $$

 

CREATE PROCEDURE CheckCredit( inCustomerNumber int )

sp: BEGIN

   

    DECLARE customerCount INT;

     -- check if the customer exists

    SELECT

        COUNT(*)

    INTO customerCount

    FROM

        customers

    WHERE

        customerNumber = inCustomerNumber;

       -- if the customer does not exist, terminate

    -- the stored procedure

    IF customerCount = 0 THEN

        LEAVE sp;

    END IF;

       -- other logic

    -- ...

END$$

 DELIMITER ;

Using LEAVE statement in loops

The LEAVE statement used to terminate a loop.

Syntax: LEAVE statement in the LOOP, REPEAT and WHILE statements.

Using LEAVE with the LOOP statement:

[label]: LOOP

    IF condition THEN

        LEAVE [label];

    END IF;

    -- statements

END LOOP [label];

 

Using LEAVE with the REPEAT statement:

[label:] REPEAT

    IF condition THEN

        LEAVE [label];

    END IF;

    -- statements

UNTIL search_condition

END REPEAT [label];

Code language: SQL (Structured Query Language) (sql)

Using LEAVE with the WHILE statement:

[label:] WHILE search_condition DO

    IF condition THEN

        LEAVE [label];

    END IF;

    -- statements

END WHILE [label];

Current loop terminated by LEAVE .

If a loop is enclosed within another loop, then can break out of both loops with a single LEAVE statement.

 

Example:-  LEAVE statement in a loop

Create a stored procedure that generates a string of integers with the number from 1 to a random number between 4 and 10:

 

DELIMITER $$

 CREATE PROCEDURE LeaveDemo(OUT result VARCHAR(100))

BEGIN

    DECLARE counter INT DEFAULT 1;

    DECLARE times INT;

    -- generate a random integer between 4 and 10

    SET times  = FLOOR(RAND()*(10-4+1)+4);

    SET result = '';

    disp: LOOP

        -- concatenate counters into the result

        SET result = concat(result,counter,',');

                -- exit the loop if counter equals times

        IF counter = times THEN

            LEAVE disp;

        END IF;

        SET counter = counter + 1;

    END LOOP;

END$$

DELIMITER ;

 

Call the LeaveDemo procedure:

CALL LeaveDemo(@result);

SELECT @result;

Outputs:

+------------------+

| @result          |

+------------------+

| 1,2,3,4,5,6,7,8, |

+------------------+

=================================================================