Returning a result set

Typically, an NZPLSQL procedure returns a simple return value. However, a NZPLSQL procedure can also be made to return a result set, which has the form of a table.

To create a NZPLSQL procedure that returns a result set:

Define the procedure with a return clause of the form RETURNS REFTABLE (<table-name>). This indicates that the procedure is to return a result set with the same layout as the specified table. The specified table must exist at the time that the procedure is created, although the table can be empty.
Within the body of the procedure, use the variable REFTABLENAME to refer to the result table.

Example

A 2-column table with the name tbl1 was defined by the following command:

CREATE TABLE tbl1 (i INT4, i2 bigint);

The layout of tbl is:


                                Data type                     Column
Column name                     schema    Data type name      Length     Scale Nulls
------------------------------- --------- ------------------- ---------- ----- ------
I                               SYSIBM    INTEGER                      4     0 Yes   
I2                              SYSIBM    BIGINT                       8     0 Yes

The following command defines a procedure with the name returntwo that returns a result set that uses tbl1 as its reference table:

DEV.SCH1(ADMIN)=> CREATE OR REPLACE PROCEDURE returntwo(timestamp) RETURNS 
REFTABLE(tbl1) LANGUAGE NZPLSQL AS 
BEGIN_PROC
  BEGIN
    EXECUTE IMMEDIATE 'INSERT INTO ' || REFTABLENAME ||' values (1,1)';
    EXECUTE IMMEDIATE 'INSERT INTO ' || REFTABLENAME ||' values (2,2)';
    RETURN REFTABLE;
  END;
  END_PROC;

Call returntwo by issuing the following statement:


DEV.SCH1(ADMIN)=> CALL PROCEDURE returntwo(now());

This produces the following result:

  Result set 1
  --------------
  I           I2                  
  ----------- --------------------
            1                    1
            2                    2
  2 record(s) selected.
  Return Status = 0

Restrictions

An NZPLSQL procedure that returns a result set is subject to the following restrictions:

The procedure can be invoked only via the CALL statement. When invoked, the database:
- Generates a table name of the form SESSION.<routinename>, where <routinename> represents the object ID of the procedure that was invoked
- Issues the following statement to create a temporary table, with no initial contents, for the result set:
```
DECLARE GLOBAL TEMPORARY TABLE <temp-table-name> 
  LIKE <table-name> WITH REPLACE ON COMMIT PRESERVE ROWS
```
- Opens a cursor on SELECT * FROM <temp-table-name> and returns the result set
To use this in a procedure, you must use the REFTABLENAME variable to obtain the name the of temporary table indicated by <temp-table-name>, and insert your results into that table. This SQL command must be invoked dynamically to use the REFTABLENAME variable.
You must return NULL in your procedure by one of the following methods:
- RETURN REFTABLE; (this is the recommended method)
- RETURN NULL;
- RETURN;
- By not specifying a RETURN clause
If you do not return NULL, the procedure returns an error.
One NZPLSQL procedure that returns a result set can call another. However, due to the temporary-table logic that it employs, if a NZPLSQL procedure that returns a result set calls itself (either directly or recursively), the results are unpredictable. Therefore, avoid designing such procedures.
Do not issue a ROLLBACK command inside the procedure body unless you first issue a COMMIT command to create the temporary table in which the result is to be stored. Otherwise, the procedure will fail.
If you run a stored procedure that runs a SELECT statement on a large data set, there might not be enough memory to hold the result. For example, the following stored procedure reads each record from the table with the name table1 and carries out an action on each record:
```
FOR rec in SELECT * from table1 LOOP
--perform processing steps
END LOOP:
```
The SELECT operation caches its results in memory or as a temporary file on disk, depending upon the size of the result set. The procedure then applies the steps in the inner processing loop. If the input table is very large, the temporary file might use up all the free disk space on the host.