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

[6 Apr 2006 12:29] Robin Schumacher
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
[6 Apr 2006 12:50] Valeriy Kravchuk
Thank you for a reasonable feture requests.
[6 Apr 2006 23:00] Konstantin Osipov
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
[7 Apr 2006 13:44] Brian Aker
Konstantin, what would it take to add this?
[11 Apr 2006 14:55] Roland Bouman
This would make http://bugs.mysql.com/bug.php?id=8299 a duplicate
[5 Feb 2008 14:18] Paul van Rossem
Any thoughts / plans on implementing this or has this reasonable request completely been snowed under?