|
|
The EXECUTE PROCEDURE statement invokes a procedure.
ISQL or Application Programs
EXECUTE PROCEDURE [:ReturnStatusVariable = ][Owner.]ProcedureName
[ ( [ActualParameter][, [ActualParameter]][...]) ]
- ReturnStatusVariable
is an integer host variable, or, for a prepared
EXECUTE PROCEDURE statement, a dynamic parameter, that receives
the return status from the procedure. ReturnStatusVariable
can only be used when invoking a procedure from an application program,
and it is always an output variable.
- [Owner.]ProcedureName
specifies the owner and the name of the procedure to execute. If an
owner name is not specified, the owner is assumed to be the current
DBEUserID.
- ActualParameter
specifies a parameter value that is passed into and/or out of the
procedure. The syntax of ActualParameter is presented
separately below.
[ParameterName = ]ParameterValue [OUTPUT[ONLY]]
- ParameterName
is the parameter name.
- ParameterValue
a value that is passed into and/or out of the procedure.
For an input only parameter, the value can be any expression that does
not include any aggregate function, add_months function, LONG column
function, TID function, local variable, procedure parameter, or built-in
variable. Column values are allowed only when the
EXECUTE PROCEDURE statement is defined in a rule.
For an OUTPUT or OUTPUT ONLY parameter, the value must be a single host
variable, or in a prepared EXECUTE PROCEDURE statement, a single
dynamic parameter.
You can omit a parameter in calling the procedure by using a comma by
itself, which is equivalent to specifying a value of NULL or the default
(if one was defined when the procedure was created). However, if a
ParameterName is specified, use of a comma by itself is
disallowed.
- OUTPUT
specifies that the caller wishes to retrieve the output value of the
parameter. OUTPUT must also have been specified for the corresponding
parameter in the CREATE PROCEDURE statement.
If OUTPUT is not specified, no output value is returned
to the caller.
- ONLY
specifies that the caller wishes to retrieve the output value of the
parameter and will not provide an input value. You must also have
specified ONLY for the corresponding parameter in the CREATE
PROCEDURE statement. ONLY should be used, when applicable, to avoid
unnecessary initialization of procedure parameters.
You cannot execute a procedure from within another
procedure.
If OUTPUT ONLY is not specified, a parameter that is not given a
value in the EXECUTE PROCEDURE statement is assigned its
default value if one was specified, or otherwise NULL if the
parameter was not declared NOT NULL.
If OUTPUT ONLY is not specified, no value is provided for a
parameter, a default is not specified, and NOT NULL is specified,
an error is returned and the procedure is not executed.
If a procedure terminates abnormally (an error occurs in
evaluating the condition in an IF or WHILE statement, or in evaluating
the expression in a parameter or variable assignment), any cursors
opened by the procedure (including KEEP cursors) are closed. Otherwise,
except in a procedure invoked by a rule, any cursor opened by the
procedure, and left open when the procedure terminates, remains
open and may therefore be accessed when the procedure is executed
again.
If OUTPUT has been specified for a parameter in both the CREATE
PROCEDURE and EXECUTE PROCEDURE statements, any changes
made to the parameter value within the procedure are returned to the
calling application. The actual parameter for an output parameter can
be a host variable or a dynamic parameter.
If you execute a procedure that returns multiple row result sets
(contains one or more SELECT statements with no INTO clause)
without using a procedure cursor, a warning is returned to the
application, no result set data is returned, and any return status
and output parameters are returned as usual.
You can execute procedures in ISQL, through application programs,
or via rules. Further information on executing a procedure from an
application is found in the ALLBASE/SQL Advanced Application
Programming Guide.. For the execution of procedures through
rules, refer to the CREATE RULE statement.
In ISQL, you cannot specify OUTPUT for a parameter. Although
return status cannot be specified in the EXECUTE PROCEDURE
statement, ISQL does report the return status. Also, within ISQL,
actual parameter values cannot include host variables.
If you attempt to execute a procedure that contains invalid
sections, ALLBASE/SQL silently revalidates the sections. You can also
use the VALIDATE statement to revalidate invalid sections in
procedures.
You can PREPARE and EXECUTE an EXECUTE
PROCEDURE statement containing dynamic parameters.
You can use EXECUTE PROCEDURE inside an EXECUTE
IMMEDIATE statement, provided the EXECUTE PROCEDURE
statement includes neither dynamic parameters nor host
variables.
If you do not specify OUTPUT for a parameter declared as OUTPUT in
the CREATE PROCEDURE statement, no value is returned.
You cannot specify OUTPUT for a parameter not declared as OUTPUT
in the CREATE PROCEDURE statement.
OUTPUT ONLY must be specified for any parameter declared as OUTPUT
ONLY in the CREATE PROCEDURE statement if an actual parameter
is provided. Use of OUTPUT ONLY improves performance, since no time
is spent initializing the parameter to the input value, default
value, or null.
Within a procedure, a single row SELECT statement (one
having an INTO clause) that returns multiple rows will assign the
first row to output parameters or local variables, and a warning is
issued. In an application, this case would generate an error.
You must have OWNER or EXECUTE authority for the procedure or DBA authority
to use this statement.
From an application program:
EXECUTE PROCEDURE :Status = Process12(:PartName, :Quantity,
:SalesPrice OUTPUT ONLY)
Within ISQL:
isql=> execute procedure Process12('Widget',150);
|