Introduction
The While loop is a loop statement that performs a block of code repeatedly as long as a condition is true. If we want to execute a block of statements(SQL Code) repeatedly based on a condition, While loop is the one supported by MySQL.
While loop Syntax
WHILE (expression)
DO
statements
END WHILE
As we know, the expression is checked by the while loop at the starting of every iteration. If the expression evaluates to right, MySQL performs statements between WHILE and END WHILE till the expression evaluates to false.
Example of WHILE loop with stored procedure
DELIMITER $$
DROP PROCEDURE IF EXISTS mySQLTestLoop $$
CREATE PROCEDURE mySQLTestLoop()
BEGIN
SET @counter = 10;
WHILE (@endPosition > 0)
DO
PRINT @counter;
SET @counter = @counter - 1;
END WHILE;
END $$
DELIMITER ;
CALL mySQLTestLoop();
DROP PROCEDURE mySQLTestLoop;
The following steps are used in the above example:
- Start delimiter to tell the MySQL client to treat the statements as an entire statement.
- Drop the procedure by using Drop with IF EXIST condition (so that MySQL doesn’t give an error if not exist).
- Use BEGIN to execute compound statements.
- Initialize the counter to 10 using a SET data type.
- Check while loop condition. It will be true if greater than 0.
- Put statements after ‘DO’, if you want to execute.
- Print counter value and decrement it by 1.
- END while and Delimiter.
- Call procedure to start execution.
- Drop procedure after execution.
If you have skills in PHP programming and you want to enhance your career in this field, a PHP certification from StudySection can help you reach your desired goals. Both beginner level and expert level PHP certification exams are offered by StudySection along with other programming certification exams.
