SQL statements that can be executed in routines and triggers
Successful execution of SQL statements in routines is subject to restrictions and conditional on certain prerequisites being met. However, it is possible to execute many SQL statements in routines and triggers.
- The SQL data access level of the statement from the following table.
- The SQL data access level of the routine specified when the routine was created.
The
following table lists all supported SQL statements, including SQL
PL control-statements, and identifies if each SQL statement can be
executed within the various types of routines. For each SQL statement
listed in the first column, each of the subsequent columns shows an X
to
indicate if the statement is executable within the routine. The final
column identifies the minimum SQL access level required to allow the
statement execution to succeed. When a routine invokes an SQL statement,
the effective SQL data access indication for the statement must not
exceed the SQL data access indication declared for the routine. For
example, a function defined as READS SQL DATA could not call a procedure
defined as MODIFIES SQL DATA. Unless otherwise noted in a footnote,
all of the SQL statements may be executed either statically or dynamically.
SQL statement | Executable in compound SQL (compiled) statements(1) | Executable in compound SQL (inlined) statements(2) | Executable in external procedures | Executable in external functions | Minimum required SQL data access level |
---|---|---|---|---|---|
ALLOCATE CURSOR | X | X | X | MODIFIES SQL DATA | |
ALTER {BUFFERPOOL, DATABASE PARTITION GROUP, FUNCTION, METHOD, NICKNAME, PROCEDURE, SEQUENCE, SERVER, TABLE, TABLESPACE, TYPE, USER MAPPING, VIEW} | X | X | MODIFIES SQL DATA | ||
ASSOCIATE LOCATORS | X | ||||
AUDIT | X | X | MODIFIES SQL DATA | ||
BEGIN DECLARE SECTION | X | X | NO SQL(3) | ||
CALL | X | X | X | X | CONTAINS SQL(12) |
CASE | X | X | CONTAINS SQL | ||
CLOSE | X | X | X | READS SQL DATA | |
COMMENT ON | X | X | X | MODIFIES SQL DATA | |
COMMIT | X(6) | X(6) | MODIFIES SQL DATA | ||
Compound SQL | X | X | X | X | CONTAINS SQL |
CONNECT(2) | |||||
CREATE {ALIAS, BUFFERPOOL, DATABASE PARTITION GROUP, DISTINCT TYPE, EVENT MONITOR, FUNCTION, FUNCTION MAPPING, GLOBAL TEMPORARY TABLE(11), INDEX(11), INDEX EXTENSION, METHOD, NICKNAME, PROCEDURE, SCHEMA, SEQUENCE, SERVER, TABLE(11), TABLESPACE, TRANSFORM, TRIGGER, TYPE, TYPE MAPPING, USER MAPPING, VIEW(11), WRAPPER } | X (8) | X | MODIFIES SQL DATA | ||
DECLARE CURSOR | X | X | X | NO SQL(3) | |
DECLARE GLOBAL TEMPORARY TABLE |
X | X | X | MODIFIES SQL DATA | |
DELETE | X | X | X | X | MODIFIES SQL DATA |
DESCRIBE(9) | X | X | READS SQL DATA | ||
DISCONNECT(4) | |||||
DROP | X(8) | X | X | MODIFIES SQL DATA | |
END DECLARE SECTION | X | X | NO SQL(3) | ||
EXECUTE | X | X | X | CONTAINS SQL(5) | |
EXECUTE IMMEDIATE | X | x | X | CONTAINS SQL(5) | |
EXPLAIN | X | X | X | MODIFIES SQL DATA | |
FETCH | X | X | X | READS SQL DATA | |
FLUSH EVENT MONITOR | X | X | MODIFIES SQL DATA | ||
FLUSH PACKAGE CACHE | X | X | MODIFIES SQL DATA | ||
FOR | X | X | READS SQL DATA | ||
FREE LOCATOR | X | X | CONTAINS SQL | ||
GET DIAGNOSTICS | X | X | READS SQL DATA | ||
GOTO | X | X | CONTAINS SQL | ||
GRANT | X | X | X | MODIFIES SQL DATA | |
IF | X | X | CONTAINS SQL | ||
INCLUDE | X | X | NO SQL | ||
INSERT | X | X | X | X | MODIFIES SQL DATA |
ITERATE | X | X | CONTAINS SQL | ||
LEAVE | X | X | CONTAINS SQL | ||
LOCK TABLE | X | X | X | CONTAINS SQL | |
LOOP | X | X | CONTAINS SQL | ||
MERGE | X | X | X | X | MODIFIES SQL DATA |
OPEN | X | X | X | READS SQL DATA(7) | |
PREPARE | X | X | X | CONTAINS SQL | |
REFRESH TABLE | X | X | MODIFIES SQL DATA | ||
RELEASE(4) | |||||
RELEASE SAVEPOINT | X | X | X | MODIFIES SQL DATA | |
RENAME TABLE | X | X | MODIFIES SQL DATA | ||
RENAME TABLESPACE | X | X | MODIFIES SQL DATA | ||
REPEAT | X | X | CONTAINS SQL | ||
RESIGNAL | X | MODIFIES SQL DATA | |||
RETURN | X | CONTAINS SQL | |||
REVOKE | X | X | MODIFIES SQL DATA | ||
ROLLBACK(6) | X | X | |||
ROLLBACK TO SAVEPOINT | X | X | X | MODIFIES SQL DATA | |
SAVEPOINT | X | MODIFIES SQL DATA | |||
select-statement | X | X | X | READS SQL DATA | |
SELECT INTO | X | X(10) | X(10) | READS SQL DATA(7) | |
SET CONNECTION(4) | |||||
SET INTEGRITY | X | MODIFIES SQL DATA | |||
SET special register | X | X | X | X | CONTAINS SQL |
SET variable | X | X | CONTAINS SQL | ||
SIGNAL | X | X | MODIFIES SQL DATA | ||
TRANSFER OWNERSHIP | X | X | MODIFIES SQL DATA | ||
TRUNCATE | X | X | MODIFIES SQL DATA | ||
UPDATE | X | X | X | MODIFIES SQL DATA | |
VALUES INTO | X | X | X | READS SQL DATA | |
WHENEVER | X | X | NO SQL(3) | ||
WHILE | X | X |
- Compound SQL (compiled) statements can be used as the body of SQL procedures, SQL functions, triggers, or as stand-alone statements.
- Compound SQL (inline) statements can be used as the body of SQL functions, SQL methods, triggers, or as stand-alone statements.
- Although the NO SQL option implies that no SQL statements can be specified, non-executable statements are not restricted.
- Connection management statements are not allowed in any routine execution context.
- This situation depends on the statement being executed. The statement specified for the EXECUTE statement must be allowed in the context of the particular SQL access level in effect. For example, if the SQL access level READS SQL DATA is in effect, the statement cannot be INSERT, UPDATE, or DELETE.
- The COMMIT statement and the ROLLBACK statement (without the TO SAVEPOINT clause) can be used in a stored procedure, but only if the stored procedure is called directly from an application, or indirectly through nested stored procedure calls from an application. If any trigger, function, method, or atomic compound statement is in the call chain to the stored procedure, a COMMIT or a ROLLBACK of a unit of work is not allowed.
- If the SQL access level READS SQL DATA is in effect, no SQL data change statement can be embedded in the SELECT INTO statement or in the cursor referenced by the OPEN statement.
- SQL routines can only issue CREATE and DROP statements statically
for indexes, tables, and views. For other objects, CREATE and DROP
statements can be issued dynamically using the following statements:
- EXECUTE IMMEDIATE
- PREPARE, followed by EXECUTE
- The DESCRIBE SQL statement has a different syntax than that of the CLP DESCRIBE command.
- This is only supported for embedded SQL routines.
- When referenced in an SQL procedure, the statement can only be executed statically.
- The procedure that is called must have the same or more restrictive level of SQL data access than the current level in effect. For example, a routine defined as MODIFIES SQL DATA can call a procedure defined as MODIFIES SQL DATA, READS SQL DATA, CONTAINS SQL, or NO SQL. A routine defined as CONTAINS SQL can call a procedure defined as CONTAINS SQL or NO SQL. The arguments specified for the procedure might also require a different data access level. For example, a scalar fullselect as an argument would require the data access level for the statement to be READS SQL DATA.
Errors
- If an executable SQL statement is encountered inside a routine defined with the NO SQL data access level, then SQLSTATE 38001 is returned.
- For other execution contexts, the SQL statements that are unsupported in any context return an SQLSTATE 38003 error.
- For other SQL statements that are not allowed in a CONTAINS SQL context, SQLSTATE 38004 is returned.
- In a READS SQL DATA context, SQLSTATE 38002 is returned.
- During creation of an SQL routine, a statement that does not match the SQL data access level returns an SQLSTATE 42985 error.