Before you begin
Many, but not all SQL statements can be executed in routines.
Execution of a particular SQL statement within a routine is dependent
on the type of routine, the implementation of the routine, the maximum
SQL access level specified for the routine, and the privileges of
the routine definer and invoker.
Determining what SQL statements
can be executed within a routine before you implement your routine
can ensure that you make the right choice of routine type and implementation
from the start.
To
successfully execute a SQL statement in a routine, the following prerequisites
must be met:
- The SQL access level of the routine must permit the execution
of the particular SQL statement.
- The SQL access level of a routine is specified in the CREATE statement
for the routine.
- Some SQL access levels are not supported for certain types of
routines. Refer to the restrictions below.
- The routine definer must have the necessary privileges to execute
the SQL statement.
- The privileges required to execute every supported SQL statement
are provided in the SQL Reference.
- No other separate restriction restricts the execution of the statement.
- Refer to the SQL Reference for a list of restrictions specific
to the given SQL statement.
The following restrictions limit the set of SQL statements that
can be executed within routines. In particular these restrictions
limit what SQL access levels can be specified for particular types
of routines:
- External functions cannot be specified with the MODIFIES SQL DATA
access level.
- External procedures that will be called from a trigger cannot
be specified with a MODIFIES SQL DATA access level.
Procedure
To determine what SQL statements can be invoked in a
particular routine, do the following:
- Determine the SQL access level of the routine. If it is
an existing routine, examine the CREATE statement that was used to
create the routine. The SQL access level clause might be explicitly
defined in the DDL as one of: NO SQL, CONTAINS SQL, READS SQL DATA,
or MODIFIES SQL DATA. If no such clause is explicitly specified,
then the default value for the routine is implied.
- For SQL procedures the default is MODIFIES SQL DATA.
- For SQL functions the default is MODIFIES SQL DATA.
- For external procedures the default is MODIFIES SQL DATA.
- For external functions the default is READS SQL DATA.
- Refer to the table in the topic, "SQL statements that can
be executed in routines". Look up the SQL statement of interest by
name.
- Check if the SQL statement is supported for the specific
type of routine and implementation.
- Verify that the required SQL access level to execute the
statement matches the SQL access level of the routine.
- Carefully read any usage notes or footnotes to ensure that
there are no other restrictions on the SQL statement execution.
Results
If
the SQL statement is indicated as being executable within a routine,
the routine SQL access level meets the prerequisites for executing
the statement within the routine, and all other prerequisites have
been met, the SQL statement should be successfully executable from
the routine.