MySQL Bugs: #8299: Allow FOR SELECT syntax extension

Bug #8299	Allow FOR SELECT syntax extension
Submitted:	3 Feb 2005 22:04	Modified:	27 May 2006 12:04
Reporter:	Paul van Rossem	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server	Severity:	S4 (Feature request)
Version:	5.0	OS:	Any (all)
Assigned to:		CPU Architecture:	Any

Description:
I find the SQL syntax for iterating over selected rows rather clumsy (although valid and correct SQL). Would suggest a syntax extension as in the Firebird / Interbase FOR SELECT ... DO statement.

Example with cursors (cf. the one in chapter 19.1.8 of the MySQL 5.0.2.A ref. manual):
CREATE PROCEDURE CREATE_ITEMS (item VARCHAR(32))
BEGIN
  DECLARE done SMALLINT DEFAULT 0;
  DECLARE base SMALLINT;
  DECLARE baseCursor CURSOR FOR SELECT BaseID FROM BaseTable;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
  OPEN baseCursor;
  REPEAT
    FETCH baseCursor INTO base;
    IF NOT done 
      THEN INSERT INTO ItemTable VALUES(item, base);
    END IF;
  UNTIL done END REPEAT;
END

Could be much more compact and intuitive written with FOR SELECT as:
CREATE PROCEDURE CREATE_ITEMS (item VARCHAR(32))
BEGIN
  DECLARE base SMALLINT;
  FOR SELECT BaseID FROM BaseTable INTO base
     DO INSERT INTO ItemTable VALUES(item, base);
END

The former gets really ugly and unreadable in more complex situations (try to nest it a couple of times!), because its needs a lot of additional concepts (handler, cursor, loop conditions), which have nothing to do with the problem space, while the latter remains comprehensible.

How to repeat:
N.A.

Hi Paul! I agree totally. The FOR syntax is supported by Oracle too, and it also gets rid of the usual problem with this REPEAT syntax: Suppose you had other work in your loop, right after the fetch, but before the UNTIL...END REPEAT, then you must ensure that your cursor would fetch at least one record. Else, the work would be executed without a successfull fetch. Of course, on could work round this by adding an IF..END IF, but this is all very cumbersome.

Another problem with this syntax is that the CONTINUE HANDLER FOR NOT FOUND can be activated by many more reasons than just the end of the cursor. So this construction is inherent buggy.

similar on verified: http://bugs.mysql.com/bug.php?id=18845&thanks=3&notify=67

Duplicate of bug #18845.