HP 3000 Manuals HP 3000 Manuals
EXECUTE PROCEDURE
The EXECUTE PROCEDURE statement invokes a procedure.
Scope
ISQL or Application Programs
Syntax
EXECUTE PROCEDURE [:ReturnStatusVariable =] [Owner.]ProcedureName
[( [ActualParameter] [, [ActualParameter]][...] )]
Parameters
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.
SQL Syntax--ActualParameter
[ParameterName =] ParameterValue [OUTPUT [ONLY]]
Parameters--ParameterDeclaration
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.
Description
* 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.
Authorization
You must have OWNER or EXECUTE authority for the procedure or DBA
authority to use this statement.
Examples
1. From an application program:
EXECUTE PROCEDURE :Status = Process12(:PartName, :Quantity,
:SalesPrice OUTPUT ONLY)
2. Within ISQL:
isql=> execute procedure Process12('Widget',150);