Return multiple values

An SPL function can return more than one value from a single row of a table. The following figure shows an SPL function that returns two column values from a single row of a table.
Figure 1: SPL function that returns two column values from a single row of a table.
CREATE FUNCTION birth_date( num INTEGER )
   RETURNING VARCHAR(30), DATE;

   DEFINE n VARCHAR(30);
   DEFINE b DATE;

   SELECT name, bdate INTO n, b FROM emp_tab
      WHERE emp_no = num;
   RETURN n, b;

END FUNCTION;

The function returns two values (a name and birthdate) to the calling routine from one row of the emp_tab table. In this case, the calling routine must be prepared to handle the VARCHAR and DATE values returned.

The following figure shows an SPL function that returns more than one value from more than one row.
Figure 2: SPL function that returns more than one value from more than one row.
CREATE FUNCTION birth_date_2( num INTEGER )
   RETURNING VARCHAR(30), DATE;
   DEFINE n VARCHAR(30);
   DEFINE b DATE;
   FOREACH cursor1 FOR
      SELECT name, bdate INTO n, b FROM emp_tab
         WHERE emp_no > num
      RETURN n, b WITH RESUME;
   END FOREACH;
END FUNCTION;
In preceding figure, the SELECT statement fetches two values from the set of rows whose employee number is higher than the number the user enters. The set of rows that satisfy the condition could contain one row, many rows, or zero rows. Because the SELECT statement can return many rows, it is placed within a cursor.
Tip: When a statement within an SPL routine returns no rows, the corresponding SPL variables are assigned NULL values.

The RETURN statement uses the WITH RESUME keywords. When RETURN WITH RESUME is executed, control is returned to the calling routine. But the next time the SPL function is called (by a FETCH or the next iteration of a cursor in the calling routine), all the variables in the SPL function keep their same values, and execution continues at the statement immediately following the RETURN WITH RESUME statement.

If your SPL routine returns multiple values, the calling routine must be able to handle the multiple values through a cursor or loop, as follows:
  • If the calling routine is an SPL routine, it needs a FOREACH loop.
  • If the calling routine is an ESQL/C program, it needs a cursor declared with the DECLARE statement.
  • If the calling routine is an external routine, it needs a cursor or loop appropriate to the language in which the routine is written.
Important: The values returned by a UDR from external databases of a local server must be built-in data types or UDTs explicitly cast to built-in types or DISTINCT types based on built-in types and explicitly cast to built-in types. In addition, you must define the UDR and all the casts in the participating databases.
An example of SQL operations you can perform across databases follows: 
database db1;
create table ltab1(lcol1 integer, lcol2 boolean, lcol3 lvarchar);
insert into ltab1 values(1, 't', "test string 1");

database db2;
create table rtab1(r1col1 boolean, r1col2 blob, r1col3 integer)
put r1col2 in (sbsp);
create table rtab2(r2col1 lvarchar, r2col2 clob) put r2col2 in (sbsp);
create table rtab3(r3col1 integer, r3col2 boolean,
r3col3 lvarchar, r3col4 circle);

create view rvw1 as select  * from rtab3;
(The example is a cross-database Insert.) 
database db1;
create view lvw1 as select * from db2:rtab2;
insert into db2:rtab1 values('t', 
filetoblob('blobfile', 'client', 'db2:rtab1', 'r1col2'), 100);
insert into db2:rtab2 values("inserted directly to rtab2",  
filetoclob('clobfile', 'client', 'db2:rtab2', 'r2col2'));
insert into db2:rtab3 (r3col1, r3col2, r3col3)
select lcol1, lcol2, lcol3 from ltab1;
insert into db2:rvw1 values(200, 'f', "inserted via rvw1");
insert into lvw1 values ("inserted via lvw1", NULL);