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
FETCHto get the result set data.
In interactive SQL, a result set procedure is called by using the
CALLstatement 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
VALUESclause which follows the parameter part of the procedure definition.
VALUESclause defines the data types of the columns in the result set and may contain an
ASclause which names the columns.
ExampleCREATE 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 decimal(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.
MODIFIES SQL DATAmust 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
RETURNstatement. The arguments to a
RETURNstatement can be null, an expression or a variable which has the
FETCHis executed, the SQL statements in the body of the result set procedure are executed until a
RETURNstatement is executed.
The execution of the result set procedure is then suspended until the next
FETCHstatement is executed for the calling cursor, then flow of control within the result set procedure continues until the next
RETURNstatement 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
FETCHmore data into the calling cursor will flag end-of-set.
Thus, a result set procedure call can be used in place of the usual
SELECTwhen 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
FETCHstatements 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
RETURNstatements. It is also permissible to use a cursor within the result set procedure to get data to be returned via a
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