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 (embedded SQL):EXEC SQL DECLARE X CURSOR FOR CALL MY_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 MY_RESULT_PROC(IN A INTEGER, IN B INTEGER) VALUES ( VARCHAR(32), INTEGER(10) ) AS ( CLIENT_NAME, CLIENT_ID ) 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 which 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. There is always at least one open cursor during the execution of a result set procedure and these statements cause all open cursors to be closed.
Note: A function or procedure which 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 embedded SQL, 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 MY_RESULT_PROC(X INTEGER) VALUES (VARCHAR(10), INTEGER) AS (TX, XP) 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 Z CURSOR FOR CALL MY_RESULT_PROC(3); EXEC SQL OPEN Z; EXEC SQL WHENEVER NOT FOUND GOTO done; EXEC SQL FETCH Z INTO :T, :X; (This will fetch 'FIRST ROW', 3) Result set procedure flow of control suspended at XP=X*2 EXEC SQL FETCH Z INTO :T, :X; (This will fetch 'SECOND ROW', 6) Result set procedure flow of control suspended at XP=X*3 EXEC SQL FETCH Z INTO :T, :X; (This will fetch 'THIRD ROW', 9) Result set procedure flow of control suspended at END; EXEC SQL FETCH Z INTO :T, :X; Flow of control exits from procedure scope and the NOT FOUND exception is raised. done: EXEC SQL CLOSE Z;
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).
Upright Database Technology AB
Voice: +46 18 780 92 00
Fax: +46 18 780 92 40