How to Correctly Implement FOR and WHILE Loops in MySQL Stored Procedures?

Understanding MySQL's FOR Loop

In MySQL, the FOR loop is an iterative statement used to execute a block of code multiple times. It allows you to iterate through a sequence of values or rows in a table.

Error in the Provided Stored Procedure

The provided stored procedure has an incorrect FOR loop syntax. The correct syntax for a FOR loop in MySQL is as follows:

FOR loop_variable IN expression1, expression2, ...
LOOP
  -- loop body
END LOOP;

Corrected Stored Procedure

The corrected version of the stored procedure with a properly implemented FOR loop:

DELIMITER $$  
CREATE PROCEDURE ABC()

   BEGIN
      DECLARE a INT Default 0 ;
      FOR i IN 1..5 LOOP
         SET a=a+1;
         select a;
      END LOOP;
END $$
DELIMITER ;

In this example, the FOR loop iterates from 1 to 5, incrementing a by 1 each iteration. The loop terminates when i reaches 6.

Alternative Loop Syntax: WHILE LOOP

In addition to the FOR loop, MySQL provides a WHILE loop, another iterative statement that executes a block of code while a specified condition is true. Its syntax is as follows:

WHILE condition LOOP
  -- loop body
END LOOP;

Example:

WHILE a < 10 LOOP
  SET a=a+1;
  select a;
END LOOP;

This WHILE loop will continue to execute until a reaches 10.

The above is the detailed content of How to Correctly Implement FOR and WHILE Loops in MySQL Stored Procedures?. For more information, please follow other related articles on the PHP Chinese website!