Cursor in UDFs ??

Wednesday, July 25, 2007

SQL PL can be categorized into two technologies.
One is "inline" SQL PL, the other, what I like to call "packaged" or
"compiled" SQL PL.
Procedures are compiled once into static SQL when they are created.

SQL Functions, Methods and Triggers as well as a standalone BEGIN ATOMIC
.... END statement (a dynamic compound) are not compiled into separate
objects. A trigger or SQL function get macro expanded into the
surrounding statement and compield within it's context.
This is very powerful technology, but also very tricky.
Consequently inline SQL PL has only a subset of the statements at its
disposal.
These are:
FOR loop (which is very close to a CURSOR)
WHILE
SET
ITERATE
CONTINUE
SIGNAL
GET DIAGNOSTICs
DECLARE variable
DECLARE condition
IF THEN ELSE

I know of only two things that inline SQL PL can do today that packaged
SQL PL cannot:
Multi column set (SET (a, b, c) = (....))
and SELECT and VALUES without INTO clauses

The former is on the todo list, the later is a historical feature.

Thanks to Serge Rielau, IBM Toronto labs..