Description:
The documentation in section http://dev.mysql.com/doc/refman/5.1/en/cursors.html is incomplete and contains a factual error. Also the example could be improved.
How to repeat:
Read http://dev.mysql.com/doc/refman/5.1/en/
Suggested fix:
"The syntax is as in embedded SQL." - no reference given...what should we be thinking of when we hear "embedded SQL"? Please provide link or context
"Cursors are currently asensitive, read-only, and non-scrolling." Except for asensitive (see next item), terms are used but not explained.
* "Read-only" should be "not updateable" - this is the term that is used in the standard. Our docs could explain that this means that our cursors are "read-only", which means that the cursor cannot be used to modify the resultset handled by the cursor.
* "non-scrolling" should be "not scrollable". This is AFAIK the usual term. "scrolling" implies something active, something that is going on right now whereas "scrollable" implies a capability, which seems to be more correct (a scrollable cursor can still happen to not be scrolling right now). The manual should explain that "not scrollable" means that the cursor is a "forward-only" cursor, meaning it can be traversed in one direction only.
It would nice to explain in http://dev.mysql.com/doc/refman/5.1/en/declare-cursors.html that a cursor can also be used for certain SHOW statements (SHOW PROCESSLIST, SHOW TABLES, alas I don't have a full list)
"Asensitive means that the server may or may not make a copy of its result table." - This is not the definition used by the standard (2003/4.32.2) - it sounds more like an part of an implementation strategy. I can paraphrase the term in a manner that I think is correct, but at the risk of getting it wrong I better cite the relevant paras:
start citation -->
A change to SQL-data is said to be independent of a cursor CR if and only if it is not made by an <update
statement: positioned> or a <delete statement: positioned> that is positioned on CR.
A change to SQL-data is said to be significant to CR if and only if it is independent of CR, and, had it been
committed before CR was opened, would have caused the table associated with the cursor to be different in
any respect.
A change to SQL-data is said to be visible to CR if and only if it has an effect on CR by inserting a row in CR,
deleting a row from CR, changing the value of a column of a row of CR, or reordering the rows of CR.
If a cursor is open, and the SQL-transaction in which the cursor was opened makes a significant change to
SQL-data, then whether that change is visible through that cursor before it is closed is determined as follows:
— If the cursor is insensitive, then significant changes are not visible.
— If the cursor is sensitive, then significant changes are visible.
— If the cursor is asensitive, then the visibility of significant changes is implementation-dependent."
<--- end citation
(paraphrase: "Asensitive means that it is implementation-dependent whether the resultset handled by a cursor is in any way modified by a change occurring to the data that underlies that resultset")
Anyway - implementation-dependent or not, our manual should explain the exact behaviour. Just the fact that it is implementation-dependent does not mean the behaviour is undefined (and if it is our manual should state that). AFAIK our current implementation is effectively "insensitive" because the resultset is stored in a temporary table.
Example:
This example uses a REPEAT loop. This is I think the worst choice for handling a CURSOR. This is because you are forced to do an explicit check inside the loop to check for cursor exhaustion. Because REPEAT has an implicit loop control variable check in the UNTIL clause, the REPEAT loop is always doing too much work - or at least, more work than is actually needed. It gets a little bit better with WHILE but this has the disadvantage that one has to repeat the FETCH statement - you need an additional fetch outside the loop to cope with the possibility that the resultset is empty:
OPEN ..
FETCH ... -- need this to cope with empty cursor
WHILE NOT done DO
-- do stuff
FETCH ...; -- need this to drive the loop
END WHILE;
(LOOP is IMO the best solution because it has no code duplication at all, at the expense of using the "ugly" unstructured loop)
