Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MySQL

Posted June 6, 2013

MySQL Cursors and Loops

By Rob Gravelle

A loop is a programming construct that executes a block of code repeatedly based on a condition. Cursors, on the other hand, are utilized to process each row returned by a SELECT statement.  In today’s article, we’ll be learning about various loop types supported by MySQL as well as gain an introduction to cursors.

More on Loops

Iterative control is reserved for stored procedures where heavy processing may take place, so you can’t use loops in regular SQL statements.  Tasks that are well suited to looping include the setting of counters, concatenating multiple strings together, and calculating mathematical algorithms.   For other tasks, such as merging multiple resultsets together, it is usually better to include additional joins and conditions to do the work all at once rather than seeding one row at a time.  It is only when this is not possible, then some form of looping, and perhaps a cursor, will be your only option.

Looping Constructs in MYSQL

There are three types of iterative statements in MySQL: WHILE, REPEAT and LOOP.  The first two correspond to the While Pre-Test, Do While Post-Test loops in other programing languages, while the LOOP construct is an animal of a different sort altogether.

The While Loop

The WHILE loop checks the condition before entering the loop’s body.  Hence, if the condition evaluates to false at the onset, the loop will never execute.  Once beyond the condition, the loop executes as long as the condition remains true.

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! We'll explore the use of cursors in more detail soon enough.

See all articles by Rob Gravelle



MySQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MySQL Forum
Topic By Replies Updated
MySQL in high availability and transction secure banking application klamor 1 August 28th, 10:24 AM
MySQL rollback UAL225 1 August 28th, 10:15 AM
Browsing a DB file that uses MySql finleytech 1 July 26th, 10:51 AM
php cookie won't pass variable to next page ITdevGirl 0 June 22nd, 12:13 PM