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 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 |