|
|
|
Valid in: SQL, ESQL, DBProc
The Execute Procedure statement invokes a database procedure.
|
|
|
The Execute Procedure statement has the following formats:
Non-dynamic version:
[EXEC SQL] EXECUTE PROCEDURE [schema.]proc_name
[(param_name=param_spec {,param_name= param_spec})] |
[(parm = SESSION.global temporary table_name)]
[RESULT ROW (variable [:indicator_var]
{, variable[:indicator_var]})]
[INTO return_status]
[EXEC SQL BEGIN;program code;
EXEC SQL END;]
Dynamic version:
[EXEC SQL] EXECUTE PROCEDURE [schema.]proc_name
[USING [DESCRIPTOR] descriptor_name]
[INTO return_status]
Specifies the name of the procedure, using a literal or a host string variable.
Is the name of a global temporary table already declared in the session in which the execute procedure is issued; it must be preceded by the SESSION qualifier.
Is a literal value, a host language variable containing the value to be passed (:hostvar), or a host language variable passed by reference (byref(:host_variable)).
|
|
|
The Execute Procedure statement executes a specified database procedure.
Database procedures can be executed from interactive SQL (the Terminal Monitor), an embedded SQL program, or from another database procedure. The first execution of the database procedure can take slightly longer than subsequent executions. For the first execution, the DBMS Server must create a query execution plan.
This statement can be executed dynamically or non-dynamically. When executing a database procedure, you generally provide values for the formal parameters specified in the definition of the procedure.
If an Execute Procedure statement includes a RESULT ROW clause, it can only be executed non-dynamically.
|
|
|
In the non-dynamic version of the EXECUTE PROCEDURE statement, parameters can be passed by value or by reference.
By value - To pass a parameter by value, specify param_name = value. When passing parameters by value, the database procedure receives a copy of the value. Values can be specified using:
The data type of the value assigned to a parameter must be compatible with the data type of the corresponding parameter in the procedure definition. Specify date data using quoted character string values, and money using character strings or numbers. If the data types are not compatible, the DBMS Server issues an error and does not execute the procedure.
By reference - To pass a parameter by reference, specify the parameter as param_name = byref(:host_variable). When passing parameters by reference, the database procedure can change the contents of the variable. Any changes made by the database procedure are visible to the calling program. Parameters cannot be passed by reference in interactive SQL.
Each param_name must match one of the parameter names in the parameter list of the definition of the procedure. Param_name must be a valid object name, and can be specified using a quoted or unquoted string or a host language variable.
|
|
|
In the dynamic version of the EXECUTE PROCEDURE statement, the descriptor_name specified in the USING clause identifies an SQL Descriptor Area (SQLDA), a host language structure allocated at runtime.
Prior to issuing the EXECUTE PROCEDURE statement, the program must place the parameter names in the sqlname fields of the SQLDA sqlvar elements and the values assigned to the parameters must be placed in the host language variables pointed to by the sqldata fields. When the statement is executed, the using clause directs the DBMS Server to use those parameter names and values.
Parameter names and values follow the same rules for use and behavior when specified dynamically as those specified non-dynamically. For example, because positional referencing is not allowed when you issue the statement non-dynamically, when you use the dynamic version, any sqlvar element representing a parameter must have entries for both its sqlname and sqldata fields. Also, the names must match those in the definition of the procedure and the data types of the values must be compatible with the parameter to which they are assigned.
The DBMS Server assigns a null or a default value to any parameter in the definition of the procedure that is not assigned an explicit value when the procedure is executed. If the parameter is not nullable and does not have a default, an error is issued.
For example, for the create statement
create procedure p (i integer not null,
d date, c varchar(100)) as ...
the following associated EXECUTE PROCEDURE statement implicitly assigns a null to parameter d.
exec sql execute procedure p (i = 123,
c = 'String');
When executing a procedure dynamically, set the SQLDA sqld field to the number of parameters that you are passing to the procedure. The sqld value tells the DBMS Server how many sqlvar elements the statement is using (how many parameters are specified). If the sqld element of the SQLDA is set to 0 when you dynamically execute a procedure, it indicates that no parameters are being specified, and if there are parameters in the formal definition of the procedure, these are assigned null or default values when the procedure executes. If the procedure parameter is not nullable and does not have a default, an error is issued.
A parameter cannot be specified in the EXECUTE PROCEDURE statement that was not specified in the CREATE PROCEDURE statement.
Return_status is an integer variable that receives the return status from the procedure. If a return_status is not specified in the database procedure, or the return statement is not executed in the procedure, 0 is returned to the calling application.
Note: The INTO clause cannot be used in interactive SQL.
The statement must be terminated according to the rules of the host language.