Execute Procedure Loops
Use an execute procedure loop to retrieve and process rows returned by a row-producing procedure using the RESULT ROW clause. The RESULT ROW clause identifies the host variables into which the values produced by the procedure return row statement are loaded. The entries in the RESULT ROW clause must match in both number and type the corresponding entries in the RESULT ROW declaration of the procedure.
The begin-end statements delimit the statements in the execute procedure loop. The code is executed once for each row as it is returned from the row-producing procedure. Statements cannot be placed between the EXECUTE PROCEDURE statement and the BEGIN statement.
During the execution of the execute procedure loop, no other statements that access the database can be issued; this causes a runtime error. However, if your program is connected to multiple database sessions, you can issue queries from within the execute procedure loop by switching to another session. To return to the outer execute procedure loop, switch back to the session in which the EXECUTE PROCEDURE statement was issued. To avoid preprocessor errors, the nested queries cannot be within the syntactic scope of the loop but must be referenced by a subroutine call or some form of a GOTO statement.
There are two ways to terminate an execute procedure loop: run it to completion or issue the ENDEXECUTE statement. A host language GOTO statement cannot be used to exit or return to the execute procedure loop.
To terminate an execute procedure loop before all rows are retrieved the application must issue the ENDEXECUTE statement. This statement must be syntactically within the begin-end block that delimits the ENDEXECUTE procedure loop.
The following example retrieves a set of rows from a row-producing procedure:
exec sql execute procedure deptsal_proc (deptid = :deptno)
result row (:deptname, :avgsal, :empcount);
exec sql begin;
browse data;
if error condition then
exec sql endexecute;
end if;
exec sql end;”
Permissions
To execute a procedure that you do not own, you must have EXECUTE privilege for the procedure, and must specify the schema parameter.
Locking
The locks taken by the procedure depend on the statements that are executed inside the procedure. All locks are taken immediately when the procedure is executed.
Performance
The first execution of the database procedure can take slightly longer than subsequent executions. For the first execution, the host DBMS may need to create a query execution plan.
EXECUTE PROCEDURE Examples
The following EXECUTE PROCEDURE examples assume the following CREATE PROCEDURE statement has been successfully executed:
EXEC SQL CREATE PROCEDURE p
(i INTEGER NOT NULL,
d DATE,
c VARCHAR(100)) AS ...
1. The following example uses a host language variable, a null constant, and an empty string.
EXEC SQL EXECUTE PROCEDURE p
(i=:ivar, d=null, c='')
INTO :retstat;
2. The following example assumes the c parameter is null and uses a null indicator for the d parameter.
EXEC SQL EXECUTE PROCEDURE p
(i=:ivar, d=:dvar:ind)
INTO :retstat;
3. The following example demonstrates the use of the WHENEVER statement for intercepting errors and messages from a database procedure.
EXEC SQL WHENEVER SQLERROR GOTO err_exit;
EXEC SQL WHENEVER SQLMESSAGE CALL SQLPRINT;
EXEC SQL EXECUTE PROCEDURE p INTO :retstat;
...
err_exit:
EXEC SQL INQUIRE_SQL (:errbug = errortext);
4. The following example demonstrates a dynamically-executed EXECUTE PROCEDURE statement. The example creates and executes the dynamic equivalent of the following statement.
EXEC SQL EXECUTE PROCEDURE enter_person
(age = :i4_var, comment = :c100_var:indicator);
Dynamic version:
EXEC SQL INCLUDE sqlda;
allocate an SQLDA with 10 elements;
sqlda.sqln = 10;
sqlda.sqld = 2;
/* 20-byte character for procedure name */
proc_name = 'enter_person';
/* 4-byte integer to put into parameter "age" */
sqlda.sqlvar(1).sqltype = int;
sqlda.sqlvar(1).sqllen = 4;
sqlda.sqlvar(1).sqldata = address(i4_var)
sqlda.sqlvar(1).sqlind = null;
sqlda.sqlvar(1).sqlname ='age';
/* 100-byte nullable character to put into the
** parameter "comment"
*/
sqlda.sqlvar(2).sqltype = char;
sqlda.sqlvar(2).sqllen = 100;
sqlda.sqlvar(2).sqldata = address(c100_var);
sqlda.sqlvar(2).sqlind = address(indicator);
sqlda.sqlvar(2).sqlname = 'comment';
EXEC SQL EXECUTE PROCEDURE :proc_name
USING DESCRIPTOR sqlda;
5. Call a database procedure, passing parameters by reference. This enables the procedure to return the number of employees that received bonuses and the total amount of bonuses conferred.
EXEC SQL EXECUTE PROCEDURE grant_bonuses
(ecount = BYREF(:number_processed),
btotal = BYREF(:bonus_total));
Last modified date: 04/03/2024