MySQL 5.1 Reference Manual :: 18 Stored Procedures and Functions :: 18.2 Stored Routine Syntax :: 18.2.9 Cursors
Section Navigation [Toggle]
- 18.2 Stored Routine Syntax
- 18.2.1 CREATE PROCEDURE and CREATE FUNCTION Syntax
- 18.2.2 ALTER PROCEDURE and ALTER FUNCTION Syntax
- 18.2.3 DROP PROCEDURE and DROP FUNCTION Syntax
- 18.2.4 CALL Statement Syntax
- 18.2.5 BEGIN ... END Compound Statement Syntax
- 18.2.6 DECLARE Statement Syntax
- 18.2.7 Variables in Stored Routines
- 18.2.8 Conditions and Handlers
- 18.2.9 Cursors
- 18.2.10 Flow Control Constructs
Simple cursors are supported inside stored procedures and functions. The syntax is as in embedded SQL. Cursors are currently asensitive, read-only, and non-scrolling. Asensitive means that the server may or may not make a copy of its result table.
Cursors must be declared before declaring handlers, and variables and conditions must be declared before declaring either cursors or handlers.
Example:
CREATE PROCEDURE curdemo()
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE a CHAR(16);
DECLARE b,c INT;
DECLARE cur1 CURSOR FOR SELECT id,data FROM test.t1;
DECLARE cur2 CURSOR FOR SELECT i FROM test.t2;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
OPEN cur1;
OPEN cur2;
REPEAT
FETCH cur1 INTO a, b;
FETCH cur2 INTO c;
IF NOT done THEN
IF b < c THEN
INSERT INTO test.t3 VALUES (a,b);
ELSE
INSERT INTO test.t3 VALUES (a,c);
END IF;
END IF;
UNTIL done END REPEAT;
CLOSE cur1;
CLOSE cur2;
END
User Comments
As of MySQL 5.0.13 you cannot make a normal SELECT on the same table for which a cursor is declared in the same compound statement.
I tried so (a select count ..into ) to get the number of rows over which to interact before oppening the cursor, and a) it always read 0!
b) it crashed the MySQL server itself if I retried calling it.
(maybe it is due to http://bugs.mysql.com/bug.php?id=13549 ) ?
Instead of REPEAT ... UNTIL as in the example from the manual you could also use LOOP ... END LOOP with an IF done LEAVE just after the fetch. This has the advantage that the exit condition has to be checked for only once instead of twice for each turn.
The done variable would also be a nice candidate for the new BOOLEAN data type with TRUE and FALSE instead of an INT with 1 and 0 (or at least BIT(1)).
See http://www.futhark.ch/mysql/130.html or http://www.futhark.ch/mysql/106.html for two examples that use these features with cursors.
I've found that due to the limitations of CONTINUE HANDLER you cannot NEST cursor loops which is a real shortcoming. In fact , you cannot have one proc loop that calls another proc loop. In fact, anytime an empty row unrelated to the loop occurs inside loop code it erroneously causes the loop to break even though there are more iterations of the loop. Cursors are not very powerful.
Hi all, as beat pointed out, LOOP is the only construct that I found useful for traversing cursors. Both WHILE and REPEAT lead to problems. See http://rpbouman.blogspot.com/2005/09/why-repeat-and-while-are-usually-not.html for examples:
Nesting cursors is possible, you just need to take care of a few things. You can either put the inner cursor in it's own BEGIN..END block, duplicating the code for the handler and the loop control, or you can reset your loop control variable inside the inner loop once the cursor is exhausted. I won't post an example here, as this would soon clutter the page. Just check it out here, it's got all the examples: http://rpbouman.blogspot.com/2005/10/nesting-mysql-cursor-loops.html
Hello everyone,
I comment the examples given on this page.
I had several working stored functions that when called from a "fetching" cursor loop didn't work (exited prematurely). The problem was that the functions were issuing SELECT statements which sometimes returned empty resultsets. This in turn executed the declared HANDLER, which interrupted the function(!) and set the variable 'done' to true -> after the first itteration the loop was acting as if the end of the data was hit.
The solution I found is somewhat cumbersome (but works!) - I've redeclared the HANDLER in each of the functions :
/* Disable DATA NOT FOUND handlers from calling functions */
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' BEGIN END;
Note that "simple cursors" means that numeric expressions in where clauses are not supported and return confusing 1329 errors (no data fetched).
This works:
declare cur1 for select table1.field1, table2.field2 from table1, table2 where table1.key=table2.key
This does not work (even if all table1.field3 values are greater than 0):
declare cur1 for select table1.field1, table2.field2 from table1, table2 where table1.key=table2.key and table1.field3 > 0;
We had the same problem, it worked nicely without the HANDLE ... workaround in MySQL 5.0.22-community-nt, but worked only with the HANDLE ... workaround in MySQL 5.0.24a-community-nt.
Add your own comment.