SQL statement data access classification for routines
Certain SQL statements can be executed in a stored procedure or in a user-defined function. Whether the statements can be executed depends on the level of SQL data access with which the routine is defined.
The following table indicates whether an SQL statement (specified in the first column) is allowed to execute in a routine with the specified SQL data access classification. The letter Y means yes.
In general, if an executable SQL statement is encountered in a routine defined as NO SQL, SQLSTATE 38001 is returned. If the routine is defined to allow some level of SQL access, SQL statements that are not supported in any context return SQLSTATE 38003. SQL statements not allowed for routines defined as CONTAINS SQL return SQLSTATE 38004, and SQL statements not allowed for READS SQL DATA return SQLSTATE 38002.
SQL statement | Level of SQL data access | |||
---|---|---|---|---|
NO SQL | CONTAINS SQL | READS SQL DATA | MODIFIES SQL DATA | |
ALLOCATE CURSOR | Y | Y | ||
ALTER | Y | |||
ASSOCIATE LOCATORS | Y | Y | ||
BEGIN DECLARE SECTION | Y1 | Y | Y | Y |
CALL | Y2 | Y2 | Y2 | |
CLOSE | Y | Y | ||
COMMENT | Y | |||
COMMIT | Y | Y | Y | |
CONNECT | Y | Y | Y | |
CREATE | Y | |||
DECLARE CURSOR | Y1 | Y | Y | Y |
DECLARE GLOBAL TEMPORARY TABLE | Y | |||
DECLARE STATEMENT | Y1 | Y | Y | Y |
DECLARE TABLE | Y1 | Y | Y | Y |
DECLARE VARIABLE | Y1 | Y | Y | Y |
DELETE | Y | |||
DESCRIBE | Y | Y | ||
DESCRIBE CURSOR | Y | Y | ||
DESCRIBE INPUT | Y | Y | ||
DESCRIBE OUTPUT | Y | Y | ||
DESCRIBE PROCEDURE | Y | Y | ||
DESCRIBE TABLE | Y | Y | ||
DROP | Y | |||
END DECLARE SECTION | Y1 | Y | Y | Y |
EXCHANGE | Y | |||
EXECUTE | Y3 | Y3 | Y | |
EXECUTE IMMEDIATE | Y3 | Y3 | Y | |
EXPLAIN | Y | |||
FETCH | Y | Y | ||
FREE LOCATOR | Y | Y | Y | |
GET DIAGNOSTICS | Y | Y | Y | |
GRANT | Y | |||
HOLD LOCATOR | Y | Y | Y | |
INCLUDE | Y1 | Y | Y | Y |
INSERT | Y | |||
LABEL | Y | |||
LOCK TABLE | Y | Y | Y | |
MERGE | Y | |||
OPEN | Y | Y | ||
PREPARE | Y | Y | Y | |
REFRESH TABLE | Y | |||
RELEASE connection | Y | Y | Y | |
RELEASE SAVEPOINT | Y | |||
RENAME | Y | |||
REVOKE | Y | |||
ROLLBACK | Y | Y | Y | |
ROLLBACK TO SAVEPOINT | Y | |||
SAVEPOINT | Y | |||
SELECT INTO | Y | Y | ||
SET CONNECTION | Y | Y | Y | |
SET host-variable | Y4 | Y | Y | |
SET special register | Y | Y | Y | |
SET transition-variable Assignment | Y4 | Y | Y | |
SIGNAL | Y | Y | Y | |
TRUNCATE | Y | |||
UPDATE | Y | |||
VALUES | Y | Y | ||
VALUES INTO | Y4 | Y | Y | |
WHENEVER | Y1 | Y | Y | Y |
Notes:
|