WL#3309: Stored Procedures: FOR statement

Let's support standard FOR ... END FOR loops.
For example:
CREATE PROCEDURE p ()
BEGIN
  DECLARE counter INT DEFAULT 0;
  FOR SELECT a, b FROM t DO
    SET counter = counter + 1;
    END FOR;
  SELECT 'There are ',counter,' rows in t';
END
//

MySQL supports LOOP, WHILE, and REPEAT, but not FOR.
In the manual, section "I.1. Restrictions on Stored Routines and Triggers",
there is a mention that "For loops are not supported".

Per-Erik Martin and Peter Gulutzan discussed it in April
2003 (subject line = "The FOR Statement") and during the
Bordeaux conference. Per-Erik felt it would be difficult,
so FOR became the only standard loop that MySQL doesn't
support. It's always been assumed that we should get
around to it eventually.

Syntax
------

We'll support what the SQL:2008 SQL/PSM document says:
"
 ::=
[   ]
FOR [  AS ]
[  [  ] CURSOR FOR ]

DO 
END FOR
[  ]
 ::= 
"

The beginning label and ending label are nothing new,
we already support them for LOOP, REPEAT, and WHILE.

A "cursor specification" is just "
[  ] [  ]". That
is, it's a SELECT.

For reasons that we will discuss below, the
"[  AS ]" and
"[  [  ] CURSOR FOR ]"
are not useful for MySQL due to other limitations.
Therefore the syntax that we actually need to support is:
"
FOR 
select_statement
DO 
END FOR
"

The SQL statement list is what you want to do for each
"fetched" row that the SELECT statement would cause.

Example:

FOR SELECT a, b FROM t DO SET counter = counter + 1; END FOR;

Equivalence
-----------

A FOR loop is equivalent to a WHILE loop with these
conversions and implied statements:
- The FOR's "cursor" is a DECLAREd cursor for a new BEGIN/END block
- The names in the FOR's SELECT list (or the FOR's cursor's
  SELECT list) are DECLAREd variables for the BEGIN/END block
- Each iteration of the FOR is an implied FETCH
- The test for the end of iterations is an implied DECLARE
  EXIT HANDLER which breaks out of the loop

Therefore this statement:

FOR SELECT a, b FROM t DO SET counter = counter + 1; END

Is equivalent to these statements:

BEGIN
  DECLARE i_CN CURSOR FOR SELECT a, b FROM t;
  OPEN i_CN;
  BEGIN
    DECLARE a INT; /* Assuming column a is an INT */
    DECLARE b INT; /* Assuming column b is an INT */
    DECLARE EXIT HANDLER FOR NOT FOUND BEGIN CLOSE i_CN; END;
    DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN CLOSE i_CN; RESIGNAL; END;
    LOOP
      FETCH i_CN INTO a, b;
      SET counter = counter + 1;
    END LOOP;
  END;
END;

(In the above, i_CN is an arbitrary implementation-dependent cursor
name which must not conflict with other names in the same stored
procedure. It may appear in error messages.)

Some observations:

The SQL statement ("SET counter = counter + 1") will happen
0 times if there are 0 rows in the result set.

Since NOT FOUND conditions are handled, it makes no sense to
declare your own handler for NOT FOUND before a FOR
-- it would be ignored.

If MySQL allowed the optional clause that specifies a cursor
(e.g. "FOR cursor1 CURSOR FOR SELECT a, b FROM t DO ..."),
you couldn't use that cursor name in an OPEN, FETCH, or CLOSE.
The open/fetch/close are implied. And MySQL doesn't
support UPDATE/DELETE ... WHERE CURSOR. Therefore there
is no point, for us, in the optional clause
"[  [  ] CURSOR FOR ]".

The statement "FOR SELECT a, a ..." would be illegal.
We would have to declare a variable named a, and then another
variable named a. That wouldn't work.

You may not use COMMIT/ROLLBACK. The assumption is that
the transaction will not end within the loop.

You should be careful with LEAVE statements, for example:
x: FOR SELECT a, b FROM t DO LEAVE x; END
Such a statement will leave an open cursor.

For Loop Variable Name
----------------------

In standard SQL, it is possible to qualify a declared
variable with the beginning label. For example:
x: BEGIN
     DECLARE v INT;
     SET v.x = 5;
   END
MySQL does not support this. Therefore the optional
"[  AS ]" clause is useless
to us. However, for the record, I include an old
email to Per-Erik Martin where it was discussed.

To: Per-Erik Martin
cc: Peter Gulutzan
Subj: The FOR Statement
Date: April 17, 2003

Hi Per-Erik,

As promised in San Jose, I've had a look at the
SQL:2003 description of the FOR statement. This
is how I understand it.

Your main question was: what is the point of
. This is a  that effectively precedes the word FOR
(never mind that it is possible to have two
 in the same place, that's
legal). You will be asking "what's the point" by
now? Well, the name can be used as a qualifier for
the variables that are implicitly DECLAREd within
the FOR loop.

There is an implicit bunch of DECLARE statements,
one for each column in the SELECT list, with the
same name and the same data type as the column in
the SELECT list.

There is an implicit FETCH (which is performed
just before the ).

For example:

DECLARE a1 INT;
FOR start_of_for AS SELECT s1 FROM table1
DO
   SET a1 = s1;
END FOR

Is equivalent to:
DECLARE a1 INT;
DECLARE s1 INT;         /* this is implied */
DECLARE cursor_name1 CURSOR FOR SELECT s1 FROM table1;
/* The above is implied. The name cursor_name1 is arbitrary. */
OPEN cursor_name1;
(loop start)
  FETCH arbitrary_cursor_name INTO v1;
  /* Exit loop if the FETCH returns "no [more] rows found" */
  (loop end)
CLOSE cursor_name1;

I found some further description in the DB2 manual.
I think the example is helpful, although I would
prefer to insist on a semicolon after the words "END FOR".

[ Editor's note: there was a quotation here; the text has
been moved to 'QA notes' field, which is not publicly visible.
See also "References" section which has URLs of other vendors' manuals. ]

[ Editor's note: putting "DECLARE a1 INT;" before the OPEN is an
error. The correct rules are shown in the "Equivalence" section. ]

Other DBMSs
-----------

DB2 allows:
 FOR ... AS cursor_name CURSOR FOR SELECT ... DO ... END FOR.
This is the same as ANSI/ISO, so we'll follow it.

Oracle and PostgreSQL allow:
 FOR ... IN [REVERSE] x..y LOOP ... END LOOP.
This is close to a WHILE loop, so it's just a redundant
syntax that saves hardly any keystrokes.
Sometimes these are called "numeric FOR loops" or "Integer
FOR loops".

Oracle and PostgreSQL allow:
 FOR ... IN SELECT ... LOOP ... END LOOP.
Oracle also allows:
 FOR rowtype_variable IN cursor_name LOOP ... END LOOP.
The rowtype_variable is for qualifying, so it's like
"for loop variable name", which we won't (usefully)
support. They use LOOP where we use DO. We don't (usefully)
support cursor_name.

SQL Server 2008 does not have a FOR loop.

References
----------

Oracle 11g FOR loops:
http://download.oracle.com/docs/cd/B14117_01/appdev.101/b10807/13_elems028.htm#i34785
http://download.oracle.com/docs/cd/B14117_01/appdev.101/b10807/06_ora.htm#sthref737

DB2 FOR loops:
http://publib.boulder.ibm.com/infocenter/db2luw/v9/index.jsp?topic=/com.ibm.db2.udb.apdv.sql.doc/doc/c0024351.htm

PostgreSQL FOR loops:
http://www.postgresql.org/docs/8.2/interactive/plpgsql-control-structures.html

BUG#18845 "Cursor traversal issues"

Forum posting "Cursor FOR loop in MySql"
http://forums.mysql.com/read.php?61,265383,265383

Forum posting "FOR LOOP IN procedure"
http://forums.mysql.com/read.php?98,77522,77522#msg-77522