ALLOCATE CURSOR statement

The ALLOCATE CURSOR statement must be used if an stored procedure returns a result set of a cursor in a called stored procedure. With the statement you’re allocating the cursor which where identified by the ASSOCIATE LOCATOR statement.

Syntax:

ALLOCATE <cursor-name> CURSOR FOR RESULT SET <locator-variable>;

Example:

create procedure responder ()
language sql
result sets 1
begin
  declare mycur cursor with return to caller for
     select firstnme, lastname from employee order by lastname, firstnme;
  open mycur;
end!


create procedure caller (out firstname varchar(20), out lastname varchar(20))
language sql
begin
  declare loc result_set_locator varying;

  call responder();
  associate result set locator(loc) with procedure responder;
  allocate cur cursor for result set loc;
  open cur;
  fetch from cur into firstname, lastname;
  close cur;
end!

DB2 Information Center: DB2 9.7 DB2 10.1 DB2 10.5