{"id":533,"date":"2009-12-27T13:25:09","date_gmt":"2009-12-27T13:25:09","guid":{"rendered":"http:\/\/www.mysqltutorial.org\/?page_id=533"},"modified":"2023-11-30T01:12:43","modified_gmt":"2023-11-30T08:12:43","slug":"loop-in-stored-procedures","status":"publish","type":"page","link":"https:\/\/www.mysqltutorial.org\/mysql-stored-procedure\/loop-in-stored-procedures\/","title":{"rendered":"MySQL LOOP"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to use MySQL LOOP<\/code> statement to run a block of code repeatedly based on a condition.<\/p>\n\n\n\n

Introduction to MySQL LOOP statement<\/h2>\n\n\n\n

The LOOP<\/code> statement allows you to execute one or more statements repeatedly.<\/p>\n\n\n\n

Here is the basic syntax of the LOOP<\/code> statement:<\/p>\n\n\n

[begin_label:] LOOP\n    statements;\nEND<\/span> LOOP<\/span> [end_label]<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The LOOP<\/code> can have optional labels at the beginning and end of the block.<\/p>\n\n\n\n
Typically, you terminate the loop when a condition is true by using IF<\/a> and LEAVE<\/a><\/code> statements as follows:<\/p>\n\n\n
[label]: LOOP\n    ...\n    -- terminate the loop<\/span>\n    IF condition THEN\n        LEAVE [label];\n    END<\/span> IF<\/span>;\n    ...\nEND<\/span> LOOP<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The loop exits when the LEAVE<\/code> statement is reached.<\/p>\n\n\n\n
Additionally, you can use the ITERATE<\/code> statement to skip the current iteration and start a new one:<\/p>\n\n\n
[label]<\/span>: LOOP<\/span>\n    ...\n    --<\/span> terminate<\/span> the<\/span> loop<\/span>\n    IF<\/span> condition<\/span> THEN<\/span>\n        ITERATE<\/span> [label]<\/span>;\n    END<\/span> IF<\/span>;\n    ...\nEND<\/span> LOOP<\/span>;<\/code><\/span>Code language:<\/span> CSS<\/span> (<\/span>css<\/span>)<\/span><\/small><\/pre>\n\n\n
Note that the LEAVE<\/code> statement functions similarly to the break<\/code> statement while the ITERATE<\/code> statement works equivalently to the continue<\/code> statement in other programming languages like PHP, C#, and Java.<\/p>\n\n\n\n
MySQL LOOP statement example<\/h2>\n\n\n\n
First, create a table<\/a> called calendars<\/code>:<\/p>\n\n\n
CREATE TABLE calendars (\n    date DATE PRIMARY KEY,\n    month INT NOT NULL<\/span>,\n    quarter INT NOT NULL<\/span>,\n    year INT NOT NULL<\/span>\n);<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
The calendars<\/code> table has four columns: <\/p>\n\n\n\n