declare cursor
Declare a cursor
Syntax
declare_cursor:
DECLARE cursor_name CURSOR [{WITH | WITHOUT} RETURN] FOR select_stmt
Description
This statement declares a cursor named cursor_name which must be a standard SQL identifier. A cursor provides the mechanism that allows a procedure or function to access the result rows from a SELECT statement one row at a time.
A cursor that is declared as WITH RETURN will, in the event that the cursor is open but not closed, return a result set to the calling program with the cursor positioned on the row as determined from the most recently executed FETCH when the procedure exits. If more than one cursor has been specified WITH RETURN, after the calling program processes the result set of the first cursor, a call to function SQLMoreResults will set up processing of the next cursor again starting at the last accessed cursor position.
The WITHOUT RETURN (default) means that no result set is returned to the calling program and that the cursor will be automatically closed upon exit if it has not already been closed.
Note that neither WITH RETURN cursors nor independent SELECT statements can be used in a stored function because functions cannot return results sets.
Note that it is also possible to directly include a SELECT statement as an independent statement in a stored procedure. The stored procedure will execute the statement as if it was a WITH RETURN cursor with the cursor position in its initial position. After the calling program completes processing the result set, it can (should) call SQLMoreResults to resume stored procedure execution at the next statement.
Example
create procedure authors_decade(in yr smallint)begin declare decade, yrborn smallint; declare fullname char(35); declare author_found, not_found boolean default false; declare authors cursor for select full_name, yr_born from author; declare continue handler for not found set not_found = true; set decade = (yr/10) * 10; open authors; find_author: loop fetch authors into fullname, yrborn; if not_found = true then leave find_author; end if; if yrborn between decade and decade+9 then select fullname, yrborn; set author_found = true; end if; end loop; if author_found = false then select "No author found born in the decade:", decade; end if;end;
See Also