CURSOR

From SQL PL Guide for DB2

Cursors

Cursors will be used in DB2 SQL PL stored procedures to perform a complex logic on a row-by-row basis. There are four basic SQL PL statements for working with cursors:

- the DECLARE CURSOR statement to define a cursor
- the OPEN statement to adjust the cursor
- the FETCH statement to retrieve the next row of the cursor
- the CLOSE statement to deactivate the cursor

Example:

create procedure DEPARTMENT_SALARY (in v_depno varchar(3), out v_totalsalary decimal(9,2))
begin
  declare SQLCODE  integer     default 0;
  declare i        decimal(9,2);
  declare mycur cursor for
      select SALARY
      from EMPLOYEE
      where WORKDEPT = v_depno;

  set v_totalsalary = 0;
  open mycur;
  fetch mycur into i;
  while SQLCODE <> 100
   do
     set v_totalsalary = v_totalsalary + i;
     fetch mycur into i;
  end while;
  close mycur;
end!

Example with a result set:

create procedure myproc ()
result sets 1
begin
  declare mycur cursor with return to client for
    select firstnme concat ' ' concat midinit concat ' ' concat lastname
    from employee;

  open mycur;
end!