Result Set Procedures

---

A result set procedure is a special type of procedure that allows a result set to be returned.

A result set procedure is called by specifying it in a cursor declaration and then using FETCH to get the result set data.

In interactive SQL, a result set procedure is called by using the CALL statement and the result set data is dealt with in the same way as a select.

Example (ESQL):

 EXEC SQL DECLARE c_1 CURSOR FOR CALL result_proc(1, 5);
 

A result set procedure is distinguished when it is created or declared by a VALUES clause which follows the parameter part of the procedure definition.

The VALUES clause defines the data types of the columns in the result set and may contain an AS clause which names the columns.

Example

 CREATE PROCEDURE barcode(IN p_ean BIGINT)
 -- result set procedure that returns book or music details for the given EAN
 RETURNS TABLE(title VARCHAR(48), creator VARCHAR(48), format VARCHAR(20), 
priced numeric(7,2), item_id INTEGER)
 READS SQL DATA
 BEGIN
    ...
 END
 

All result set procedure parameters have mode IN, therefore, any data returned from a result set procedure is returned via the procedure's result set.

The option MODIFIES SQL DATA must not be specified for the access clause of a result set procedure, see Routine Access Clause.

Note: A function or procedure that has MODIFIES SQL DATA specified for its access clause must not be invoked from within a result set procedure.

A result set procedure must not execute a COMMIT or ROLLBACK statement, because this would close the cursor that is used in order to call the result set procedure.

Note: A function or procedure that executes a COMMIT or ROLLBACK statement must not be invoked from within a result set procedure.

A row in the result set of a result set procedure is returned by executing the RETURN statement. The arguments to a RETURN statement can be NULL, an expression or a variable which has the ROW data type.

When a FETCH is executed, the SQL statements in the body of the result set procedure are executed until a RETURN statement is executed.

The execution of the result set procedure is then suspended until the next FETCH statement is executed for the calling cursor, then flow of control within the result set procedure continues until the next RETURN statement is encountered, or until the end of the procedure is reached.

After flow of control has exited from the scope of a result set procedure the next attempt to FETCH more data into the calling cursor will flag end-of-set.

Thus, a result set procedure call can be used in place of the usual SELECT when declaring a cursor.

The following example, using ESQL, is intended to demonstrate how execution within the result set procedure proceeds, and is suspended, in response to FETCH statements being executed for the calling cursor:

 EXEC SQL
    CREATE PROCEDURE result_proc(x INTEGER)
    RETURNS TABLE (txt VARCHAR(10), xp INTEGER)
    CONTAINS SQL
    BEGIN
       DECLARE xp INTEGER DEFAULT x;
 
       RETURN ('FIRST ROW', xp);
 
       SET xp = x * 2;
       RETURN ('SECOND ROW', xp);
 
       SET xp = x * 3;
       RETURN ('THIRD ROW', XP);
    END;
 
 EXEC SQL DECLARE c_1 CURSOR FOR CALL result_proc(3);
 EXEC SQL OPEN c_1;
 
 EXEC SQL WHENEVER NOT FOUND GOTO done;
 
 EXEC SQL FETCH c_1
             INTO :T, :X;
 (This will fetch 'FIRST ROW', 3)
 Result set procedure flow of control suspended at XP=X*2
 
 EXEC SQL FETCH c_1
             INTO :T, :X;
 (This will fetch 'SECOND ROW', 6)
 Result set procedure flow of control suspended at XP=X*3
 
 EXEC SQL FETCH c_1
             INTO :T, :X;
 (This will fetch 'THIRD ROW', 9)
 Result set procedure flow of control suspended at END;
 
 EXEC SQL FETCH c_1
             INTO :T, :X;
 Flow of control exits from procedure scope and the NOT FOUND exception is 
raised.
 
 done:
 EXEC SQL CLOSE c_1;
 

More typically, a loop construct would be used in the result set procedure to deal with RETURN statements. It is also permissible to use a cursor within the result set procedure to get data to be returned via a SELECT.

Closing the cursor for a result set procedure will close any open cursors declared within it and no further execution of the procedure will occur.

Reopening the cursor will start execution of the result set procedure afresh from the beginning (i.e. no state information is saved between a close and reopen).

Mimer Information Technology AB Voice: +46 18 780 92 00 Fax: +46 18 780 92 40 info@mimer.se