FOR statement

The FOR loop will be used very often as a simple read-only cursor. You’ve just to specify the select statement after the FOR <loopname> AS keywords. Optional there’s the possiblity to specify a name for this cursor. If you don’t use the cursor option DB2 will generate a unique name for this cursor. The end of the looped section is marked with the END FOR statement.

It’s not possible to reference the defined cursor outside of this for loop. Accessing this cursor by the OPEN, FETCH, or CLOSE statement is not supported.


for <loop-name> as [<cursor-name>] cursor [with hold] for <select-statement>
 end for;


 create table mytab (zipcode char(4), city char(30))!
 create table mytab2 (zip_and_city char(35))!

 insert into mytab values ('9500','Paris')!

 create procedure myproc
  for i as mycursor cursor for select zipcode, city from mytab
      insert into mytab2 values (zipcode concat ' ' concat city);
  end for;

Link to the DB2 Information Center: DB2 9.7 DB2 10.1 DB2 10.5