DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1; OPEN cursor1; cursor_loop: LOOP FETCH cursor1 INTO var1, var2, var3; IF done=1 THEN LEAVE cursor_loop; END IF; -- Do something with the row fetched.
END LOOP cursor_loop; CLOSE cursor1; SET done=0; As a best practice, you should always reset your status variable to 0 after the cursor loop terminates; otherwise, subsequent or nested cursor loops may terminate prematurely.
If one of these conditions occurs, the specified statement executes.
Some people check for this string literal to detect a NOT FOUND condition, but I personally feel that the NOT FOUND constant is clearer.When a CONTINUE handler for the NOT FOUND condition fires, that's your cue to set the value of a status variable so that you can terminate the cursor loop.Once the cursor is opened, it does not have sensitivity to inserts, updates, or deletions of rows underlying its result table.The cursor is read-only and a temporary result is created.The DECLARE statement declares a cursor and associates it with a SELECT statement that retrieves the rows to be traversed by the cursor.
Cursor declarations must appear after variable and condition declarations but before handler declarations.
When you sign in to comment, IBM will provide your email, first name and last name to DISQUS.
With all the processing power that cursors provide, there is a downside in that large resultsets can be as slow as molasses to process.
As with all database tools, you must carefully consider how cursors meet the needs of your users and determine whether using them within a procedure or function will provide the best solution to the problem you are attempting to solve.
There are many tasks well suited to cursors, but there are some common uses.