The
EXECUTE statement executes a prepared SQL statement.
Invocation
This statement can only be embedded
in an application program. It is an executable statement that cannot
be dynamically prepared.
Authorization
For each global variable
used as an
expression in the USING clause
or in the expression for an
array-index,
the privileges held by the authorization ID of the statement must
include one of the following authorities:
- READ privilege on the global variable that is not defined in a
module
- EXECUTE privilege on the module of the global variable that is
defined in a module
For each global variable used as an
assignment-target,
the privileges held by the authorization ID of the statement must
include one of the following authorities:
- WRITE privilege on the global variable that is not defined in
a module
- EXECUTE privilege on the module of the global variable that is
defined in a module
For statements where authorization checking is performed
at statement execution time (DDL, GRANT, and REVOKE statements), the
privileges held by the authorization ID of the statement must include
those required to execute the SQL statement specified by the PREPARE
statement. The authorization ID of the statement might be affected
by the DYNAMICRULES bind option.
For statements where authorization
checking is performed at statement preparation time (DML), no further
authorization checking is performed on the SQL statement specified
by the PREPARE statement.
Syntax
>>-EXECUTE--statement-name-------------------------------------->
>--+----------------------------------------------+------------->
| .-,-----------------. |
| V | |
'-INTO--+---assignment-target-+--------------+-'
'-DESCRIPTOR--result-descriptor-name-'
>--+----------------------------------------------+------------><
| .-,-----------------------. |
| V | |
'-USING--+---+-input-host-variable-+-+-------+-'
| | (1) | |
| '-expression----------' |
'-DESCRIPTOR--input-descriptor-name-'
assignment-target
|--+-global-variable-name-------------------+-------------------|
+-host-variable-name---------------------+
+-SQL-parameter-name---------------------+
+-SQL-variable-name----------------------+
+-transition-variable-name---------------+
+-array-variable-name--[--array-index--]-+
'-field-reference------------------------'
Notes:
- An expression other than host-variable can
only be used when the EXECUTE statement is used within a compound
SQL (compiled) statement.
Description
- statement-name
- Identifies the prepared statement to be executed. The statement-name must
identify a statement that was previously prepared, and the prepared
statement cannot be a SELECT statement.
- INTO
- Introduces a list of targets which are used to receive
values from output parameter markers in the prepared statement. Each
assignment to a target is made in sequence through the list. If an
error occurs on any assignment, the value is not assigned to the target,
and no more values are assigned to targets. Any values that have already
been assigned to targets remain assigned.
For a dynamic CALL
statement, parameter markers appearing in OUT and INOUT arguments
to the procedure are output parameter markers. If any output parameter
markers appear in the statement, the INTO clause must be specified
(SQLSTATE 07007).
- assignment-target
- Identifies one or more targets for the assignment of output values.
The first value in the result row is assigned to the first target
in the list, the second value to the second target, and so on.
If
the data type of an assignment-target is
a row type, then there must be exactly one assignment-target specified
(SQLSTATE 428HR), the number of columns must match the number of fields
in the row type, and the data types of the columns of the fetched
row must be assignable to the corresponding fields of the row type
(SQLSTATE 42821).
If the data type of an assignment-target is
an array element, then there must be exactly one assignment-target specified.
- global-variable-name
- Identifies the global variable that is the assignment target.
- host-variable-name
- Identifies the host variable that is the assignment target. For
LOB output values, the target can be a regular host variable (if it
is large enough), a LOB locator variable, or a LOB file reference
variable.
- SQL-parameter-name
- Identifies the routine parameter that is the assignment target.
- SQL-variable-name
- Identifies the SQL variable that is the assignment target. SQL
variables must be declared before they are used.
- transition-variable-name
- Identifies the column to be updated in the transition row. A transition-variable-name must
identify a column in the subject table of a trigger, optionally qualified
by a correlation name that identifies the new value.
- array-variable-name
- Identifies an SQL variable, SQL
parameter, or global variable of an array type.
- array-index
- An expression that specifies which element in the array will be
the target of the assignment. For an ordinary array, the array-index expression
must be assignable to INTEGER (SQLSTATE 428H1) and cannot be the null
value. Its value must be between 1 and the maximum cardinality defined
for the array (SQLSTATE 2202E). For an associative array, the array-index expression
must be assignable to the index data type of the associative array
(SQLSTATE 428H1) and cannot be the null value.
- field-reference
- Identifies the field within a row type value that is the assignment
target. The field-reference must be specified
as a qualified field-name where the qualifier
identifies the row value in which the field is defined.
- DESCRIPTOR result-descriptor-name
- Identifies an output SQLDA that must contain a valid description
of host variables.
Before the EXECUTE statement is processed,
the user must set the following fields in the input SQLDA:
- SQLN to indicate the number of SQLVAR occurrences provided in
the SQLDA
- SQLDABC to indicate the number of bytes of storage allocated for
the SQLDA
- SQLD to indicate the number of variables used in the SQLDA when
processing the statement
- SQLVAR occurrences to indicate the attributes of the variables.
The SQLDA must have enough storage to contain all SQLVAR
occurrences. Therefore, the value in SQLDABC must be greater than
or equal to 16 + SQLN*(N), where N is the length of an SQLVAR occurrence.
If
LOB or structured data type output data must be accommodated, there
must be two SQLVAR entries for every output parameter marker.
SQLD
must be set to a value greater than or equal to zero and less than
or equal to SQLN.
- USING
- Introduces a list of variables or expressions for which values
are substituted for the input parameter markers in the prepared statement.
For a dynamic CALL statement, parameter markers appearing in IN
and INOUT arguments to the procedure are input parameter markers.
For all other dynamic statements, all the parameter markers are input
parameter markers. If any input parameter markers appear in the statement,
the USING clause must be specified (SQLSTATE 07004).
- input-host-variable, ...
- Identifies a host variable that is declared in the program in
accordance with the rules for declaring host variables. The number
of variables must be the same as the number of input parameter markers
in the prepared statement. The nth variable
corresponds to the nth parameter marker
in the prepared statement. Locator variables and file reference variables,
where appropriate, can be provided as the source of values for parameter
markers.
- expression
- Identifies an expression to be used as the input for the corresponding
input parameter marker in the prepared statement. An expression
other than a host-variable can only be specified
when the EXECUTE statement is issued within a compound SQL (compiled)
statement.
- DESCRIPTOR input-descriptor-name
- Identifies an input SQLDA that must contain a valid description
of host variables.
Before the EXECUTE statement is processed,
the user must set the following fields in the input SQLDA:
- SQLN to indicate the number of SQLVAR occurrences provided in
the SQLDA
- SQLDABC to indicate the number of bytes of storage allocated for
the SQLDA
- SQLD to indicate the number of variables used in the SQLDA when
processing the statement
- SQLVAR occurrences to indicate the attributes of the variables.
The SQLDA must have enough storage to contain all SQLVAR
occurrences. Therefore, the value in SQLDABC must be greater than
or equal to 16 + SQLN*(N), where N is the length of an SQLVAR occurrence.
If
LOB or structured data type input data must be accommodated, there
must be two SQLVAR entries for every parameter marker.
SQLD
must be set to a value greater than or equal to zero and less than
or equal to SQLN.
Notes
- Before the prepared statement is executed, each input parameter
marker is effectively replaced by the value of its corresponding variable
or expression. For a typed parameter marker, the attributes of the
target variable or expression are those specified by the CAST specification.
For an untyped parameter marker, the attributes of the target variable
or expression are determined according to the context of the parameter
marker.
Let V denote an input variable or expression that corresponds
to parameter marker P. The value of V is assigned to the target variable
for P in accordance with the rules for assigning a value to a column.
Thus:
- V must be compatible with the target.
- If V is a string, its length must not be greater than the length
attribute of the target.
- If V is a number, the absolute value of its integral part must
not be greater than the maximum absolute value of the integral part
of the target.
- If the attributes of V are not identical to the attributes of
the target, the value is converted to conform to the attributes of
the target.
When the prepared statement is executed, the value used
in place of P is the value of the target variable for P or the result
of the target expression for P. For example, if V is CHAR(6) and the
target is CHAR(8), the value used in place of P is the value of V
padded with two blanks.
- For a dynamic CALL statement, after the prepared statement is
executed, the returned value of each OUT and INOUT argument is assigned
to the assignment target corresponding to the output parameter marker
used for the argument. For a typed parameter marker, the attributes
of the target variable are those specified by the CAST specification.
For an untyped parameter marker, the attributes of the target variable
are those specified by the definition of the parameter of the procedure.
Let V denote an output assignment target that corresponds to parameter
marker P, which is used for argument A of a procedure. The value of
A is assigned to V in accordance with the rules for retrieving a value
from a column. Thus:
- V must be compatible with A.
- If V is a string, its length must not be less than the length
of A, or the value of A will be truncated.
- If V is a number, the maximum absolute value of its integral part
must not be less than the absolute value of the integral part of A.
- If the attributes of V are not identical to the attributes of
A, the value of A is converted to conform to the attributes of V.
- Dynamic SQL statement caching: The information required
to execute dynamic and static SQL statements is placed in the database
package cache when static SQL statements are first referenced or when
dynamic SQL statements are first prepared. This information stays
in the package cache until it becomes invalid, the cache space is
required for another statement, or the database is shut down.
When
an SQL statement is executed or prepared, the package information
relevant to the application issuing the request is loaded from the
system catalog into the package cache. The actual executable section
for the individual SQL statement is also placed into the cache: static
SQL sections are read in from the system catalog and placed in the
package cache when the statement is first referenced; dynamic SQL
sections are placed directly in the cache after they have been created.
Dynamic SQL sections can be created by an explicit statement, such
as PREPARE or EXECUTE IMMEDIATE. Once created, sections for dynamic
SQL statements may be recreated by an implicit prepare of the statement
by the system if the original section has been deleted for space management
reasons, or has become invalid due to changes in the environment.
Each
SQL statement is cached at the database level and can be shared among
applications. Static SQL statements are shared among applications
using the same package; dynamic SQL statements are shared among applications
using the same compilation environment, and the exact same statement
text. The text of each SQL statement issued by an application is cached
locally within the application for use if an implicit prepare is required.
Each PREPARE statement in the application program can cache one statement.
All EXECUTE IMMEDIATE statements in an application program share the
same space, and only one cached statement exists for all these EXECUTE
IMMEDIATE statements at a time. If the same PREPARE or any EXECUTE
IMMEDIATE statement is issued multiple times with a different SQL
statement each time, only the last statement will be cached for reuse.
The optimal use of the cache is to issue a number of different PREPARE
statements once at the start of the application, and then to issue
an EXECUTE or OPEN statement as required.
When
dynamic SQL statements are cached, a statement can be reused over
multiple units of work without needing to prepare the statement again,
unless the SQL statements prepared in a package are bound with the KEEPDYNAMIC
NO option. The system recompiles the statement if necessary
when environment changes occur.
The following events are examples
of environment or data object changes that can cause cached dynamic
statements to be implicitly prepared on the next PREPARE, EXECUTE,
EXECUTE IMMEDIATE, or OPEN request:
- ALTER FUNCTION
- ALTER METHOD
- ALTER NICKNAME
- ALTER PROCEDURE
- ALTER SERVER
- ALTER TABLE
- ALTER
TABLESPACE
- ALTER TYPE
- CREATE FUNCTION
- CREATE FUNCTION MAPPING
- CREATE INDEX
- CREATE METHOD
- CREATE PROCEDURE
- CREATE TABLE
- CREATE TEMPORARY TABLESPACE
- CREATE TRIGGER
- CREATE TYPE
- DROP (all objects)
- RUNSTATS on any table or index
- Any action that causes a view to become inoperative
- UPDATE of statistics in any system catalog table
- SET CURRENT DEGREE
- SET PATH
- SET QUERY OPTIMIZATION
- SET SCHEMA
- SET SERVER OPTION
The following list outlines the behavior that can be expected
from cached dynamic SQL statements:
- PREPARE Requests: Subsequent preparations
of the same statement do not incur the cost of compiling the statement
if the section is still valid. The cost and cardinality estimates
for the current cached section are returned. These values might differ
from the values returned from any previous PREPARE for the same SQL
statement. You do not need to issue a PREPARE statement subsequent
to a COMMIT or ROLLBACK statement, unless the statement is associated
with a package that was bound with KEEPDYNAMIC NO.
- EXECUTE Requests: EXECUTE statements may occasionally incur
the cost of implicitly preparing the statement if it has become invalid
since the original PREPARE. If a section is implicitly prepared, it
will use the current environment and not the environment of the original
PREPARE statement.
- EXECUTE IMMEDIATE Requests: Subsequent EXECUTE IMMEDIATE
statements for the same statement will not incur the cost of compiling
the statement if the section is still valid.
- OPEN Requests: OPEN requests for dynamically defined cursors
may occasionally incur the cost of implicitly preparing the statement
if it has become invalid since the original PREPARE statement. If
a section is implicitly prepared, it will use the current environment
and not the environment of the original PREPARE statement.
- FETCH Requests: No behavior changes should be expected.
- ROLLBACK: Only those dynamic SQL statements
prepared or implicitly prepared during the unit of work affected by
the rollback operation are invalidated. Inactive dynamic SQL statements
associated with a package bound with KEEPDYNAMIC NO are
removed from the application SQL context after a ROLLBACK opeation
and must be explicitly prepared again before the application can execute
them. Dynamic SQL statements are still cached at the database level,
so a subsequent PREPARE request does not incur the cost of compiling
the statement if the section is still valid.
- COMMIT: Dynamic SQL statements are not
be invalidated, but any acquired locks are be freed. Cursors not defined
with the WITH HOLD option are closed and their locks
freed. Open cusors defined with the WITH HOLD option
hold onto their package and section locks to protect the active section
both during and after commit processing. Dynamic SQL statements bound
with the KEEPDYNAMIC NO option are not in a prepared
state after a transaction boundary and must be explicitly prepared
again before the application can execute them. SELECT statements
prepared for an open cursor defined with the WITH HOLD option
remain in a prepared state until a transaction boundary is hit where
the cursor is closed. Inactive dynamic SQL statements associated with
a package bound with KEEPDYNAMIC NO are removed from
the application SQL context after a commit operation and must be explicitly
prepared again before the application can execute them.
If an error occurs during an implicit prepare, an error
will be returned for the request causing the implicit prepare (SQLSTATE
56098).
Examples
Example 1: In this C example,
an INSERT statement with parameter markers is prepared and executed.
Host variables
h1 - h4 correspond to the format of
TDEPT.
strcpy (s,"INSERT INTO TDEPT VALUES(?,?,?,?)");
EXEC SQL PREPARE DEPT_INSERT FROM :s;
.
.
(Check for successful execution and put values into :h1, :h2, :h3, :h4)
.
.
EXEC SQL EXECUTE DEPT_INSERT USING :h1, :h2,
:h3, :h4;
Example 2: This EXECUTE statement
uses an SQLDA.
EXECUTE S3 USING DESCRIPTOR :sqlda3
Example
3: Given a procedure to award an employee a bonus:
CREATE PROCEDURE GIVE_BONUS (IN EMPNO INTEGER,
IN DEPTNO INTEGER,
OUT CHEQUE INTEGER,
INOUT BONUS DEC(6,0))
...
Dynamically call the procedure from a C application.
The procedure takes the following host variables as input:
- employee, the ID number of the employee
- dept, the department number
- bonus, the bonus to be awarded to the
employee
The procedure returns the following values to the host variables:
- cheque_no, the ID number from the cheque
- bonus, the actual bonus amount (after
any adjustments)
strcpy (s, "CALL GIVE_BONUS(?, ?, ?, ?)");
EXEC SQL PREPARE DO_BONUS FROM :s;
.
.
/* Check for successful execution and put values into
:employee, :dept, and :bonus */
.
.
EXEC SQL EXECUTE DO_BONUS INTO :cheque_no, :bonus
USING :employee, :dept, :bonus;
.
.
/* Check for successful execution and process the
values returned in :cheque_no and :bonus */