MySQL Bugs: #18845: Cursor traversal issues

Bug #18845	Cursor traversal issues
Submitted:	6 Apr 2006 12:29	Modified:	6 Apr 2006 12:50
Reporter:	Robin Schumacher	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Stored Routines	Severity:	S4 (Feature request)
Version:	5.0	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
From a recent survey done on feature inadequacies:

Cursor traversal
============
Ok, it's  like this. We can declare and traverse cursors, but we need a NOT FOUND handler to see if our cursor's exhausted. What's worse, we have to be very careful when nesting cursors, and doing a couple of SELECT..INTO's inside the cursor loop is unneccesary complicated too because the handler does not distinguish between the different statements causing the NOT FOUND condition to be raised.
This means you need all kinds of nesting, extra BEGIN..END and handlers and careful loop control variable handling. Now this can all be done, but more often people just get it wrong (look in the cursor and sp forum and you'll see what i mean) and decide mysql does not support it.
A lot could be improved when mysql would support the FOR loop for cursors,

FOR (var1, var2, var3) IN (
    SELECT col1,col2,col3
    FROM   t
) LOOP
END LOOP

or simple cursor attributes like oracle does:

WHILE mycursor%FOUND LOOP

How to repeat:
See above

Thank you for a reasonable feture requests.

The standard-compliant syntax is:
[<beginning_label>:]
FOR <loop variable name> AS [<Cursor name>  {SENSITIVE|ASENSITIVE|INSENSITIVE}]
CURSOR FOR] <query expression> [ORDER BY clause] [updateability clause]
DO
  <SQL statement>(s)
END FOR [<end_label>]

Effectively, a Cursor is open when the loop begins, fetched for each row of the result set, and closed when the loop ends. Execution of the loop body takes place for each row of the result set.
Source: SQL99 Complete, Really

MySQL doesn't support sensitive/asensitive/insensitive clause and FOR UPDATE clause, but we can add support for the following syntax:
FOR <loop variable name> AS [<Cursor name> CURSOR FOR] 
<query expression> [ORDER BY clause] [updateability clause]
DO
  <SQL statement>(s)
END FOR

Konstantin, what would it take to add this?

This would make http://bugs.mysql.com/bug.php?id=8299 a duplicate

Any thoughts / plans on implementing this or has this reasonable request completely been snowed under?