MySQL Cursor Loops – the easy way

If you want to use a cursor, you are going to need a loop. All the examples on the MySQL website are a bit convoluted, have unnecessary flags and checks.

This is a better way to do it:

CREATE PROCEDURE foo()
BEGIN
  DECLARE c1 CURSOR FOR SELECT .....;

  OPEN c1;
  BEGIN
    DECLARE EXIT HANDLER FOR NOT FOUND CLOSE c1;
    LOOP
      FETCH c1 INTO ...;
      /* do whatever */
    END LOOP;
  END;

END

The cursor open and close statements are kept together, there are no unnecessary flags, variables, conditionals, etc. This construct can be easily nested, and is simple to read, simple to write and simple to maintain.