Stored procedures with result set

With IBM® DB2®, you can execute stored procedures returning a result set. To do so, you must declare a cursor and fetch the rows:

MAIN
   DEFINE i, n INTEGER
   DEFINE d DECIMAL(6,2)
   DEFINE c VARCHAR(200)
   DATABASE test1
   CREATE TABLE tab1 ( c1 INTEGER, c2 DECIMAL(6,2), c3 VARCHAR(200) )
   INSERT INTO tab1 VALUES ( 1, 123.45, 'aaaaaa' )
   INSERT INTO tab1 VALUES ( 2, 123.66, 'bbbbbbbbb' )
   INSERT INTO tab1 VALUES ( 3, 444.77, 'cccccc' )
   EXECUTE IMMEDIATE "create procedure proc2( in key integer )"
                || " result sets 1"
                || " language sql"
                || "  begin"
                || "  declare c1 cursor with return for"
                || "    select * from tab1 where c1 > key;"
                || "  open c1;"
                || "  end"
   DECLARE curs CURSOR FROM "call proc2(?)"
   LET i = 1
   FOREACH curs USING i INTO n, d, c 
       DISPLAY n, d, c
   END FOREACH
END MAIN