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