Cursors

Cursors are uni-directional result sets that can be iterated and its data fetched into variables.

A cursor first needs to be declared using a name and the DECLARE CURSOR statement and can be used afterward.

When using a cursor, you have to OPEN the cursor first, FETCH the data and CLOSE the cursor when you're done with it.

Cursors differ from a FOR SELECT loop in the way cursors fetch data. While a FOR SELECT loop automatically fetches on each iteration of the loop, you have to manually fetch data from cursors to advance to the next row of the result set.

There's also the Cursor-namespace, which holds a whole set of other Cursor functions, which can be used with the declared cursors.

Example

execute udsblock()

returns(out_int integer)

as

declare mycursor cursor for (select v from DUMMY_ROWS_10 order by v desc);

begin

  /* open the cursor result set */

  open mycursor;

  /* fetch data from the first row */

  fetch mycursor into out_int;

  /* close the cursor */

  close mycursor;

end