The following pragmas are provided specifically for routines:
DEBUG ROUTINE to output additional information or error information
DEBUG VALUE to output additional information for the SQL statements SET in routines and RETURN in UDFs
LOOP LIMIT to limit the number of loop passes
See section "Pragmas and annotations".
The DEBUG ROUTINE and LOOP LIMIT pragmas are only effective ahead of the SQL statement CALL and ahead of the DML statements DECLARE CURSOR, DELETE, INSERT, MERGE, SELECT, and UPDATE. When specified ahead of DML statements, these pragmas have an effect on all UDFs and the routines of the DML statement these contain. When placed ahead of SQL statements, these pragmas have no effect in a routine.
Other pragmas can also be used in the CALL statement and in routines.
Pragmas EXPLAIN, CHECK, LIMIT ABORT_EXECUTION
These pragmas are effective ahead of the SQL statement CALL and ahead of the DML statements DECLARE CURSOR, DELETE, INSERT, MERGE, SELECT, and UPDATE. When specified ahead of DML statements, they have an effect on all UDFs of the DML statement and all routines contained in these UDFs. When one of these pragmas precedes an SQL statement in a routine, it is ignored.
Pragmas ISOLATION LEVEL, LOCK MODE
When these pragmas precede a CALL statement, they only influence the possibly complex call values of the CALL statement.
These pragmas can also precede SQL statements in routines. They then have the effect described under DECLARE CURSOR, DELETE, INSERT, MERGE, SELECT, and UPDATE.
When these pragmas precede an IF statement, they only influence the conditions of the IF statement. These pragmas can also be specified ahead of the statements contained in the IF statement.
In the case of the SET statement, these pragmas influence the evaluation of the expression on the right-hand side of the assignment.
When these pragmas precede a LOOP, LEAVE, or ITERATE statement, they are ignored.
When these pragmas precede a FOR statement, they only influence the cursor definition of the FOR statement. These pragmas can also be specified ahead of the SQL statements contained in the FOR statement.
When these pragmas precede a WHILE statement, they only influence the condition of the WHILE loop. These pragmas can also be specified ahead of the SQL statements contained in the WHILE statement.
When these pragmas are to influence the UNTIL condition of a REPEAT statement, they must be specified immediately ahead of UNTIL (not ahead of REPEAT). These pragmas can also be specified ahead of the SQL statements contained in the REPEAT statement.
When these pragmas precede a CASE statement, they only influence the expressions outside of the THEN and ELSE statement blocks. These pragmas can also be specified ahead of the SQL statements contained in the CASE statement.
In the case of the RETURN statement, these pragmas have an effect on the evaluation of the RETURN value.
In the case of all other statements in routines, these pragmas have no effect.
Pragmas IGNORE, JOIN, KEEP JOIN ORDER, OPTIMIZATION, SIMPLIFICATION, USE
When one of these optimization pragmas precedes a CALL statement, it only influences the optimization of the possibly complex call values of the CALL statement.
These pragmas can also precede SQL statements of a routine. They then implement the optimization described under DECLARE CURSOR, DELETE, INSERT, MERGE, SELECT, and UPDATE.
When these pragmas precede an IF statement, they only influence the optimization of the IF statement's conditions. These pragmas can also be specified ahead of the statements contained in the IF statement.
In the case of the SET statement, these pragmas influence the optimization of the expression on the right-hand side of the assignment.
When these pragmas precede a LOOP, LEAVE, or ITERATE statement, they are ignored.
When these pragmas precede a FOR statement, they only influence the cursor definition of the FOR statement. These pragmas can also be specified ahead of the SQL statements contained in the FOR statement.
When these pragmas precede a WHILE statement, they only influence the condition of the WHILE loop. These pragmas can also be specified ahead of the SQL statements contained in the WHILE statement.
When these pragmas are set to influence the UNTIL condition of a REPEAT statement, they must be specified immediately ahead of UNTIL (not ahead of REPEAT). These pragmas can also be specified ahead of the SQL statements contained in the REPEAT statement.
When these pragmas precede a CASE statement, they only influence the expressions outside of the THEN and ELSE statement blocks. These pragmas can also be specified ahead of the SQL statements contained in the CASE statement.
In the case of the RETURN statement, these pragmas have an effect on the evaluation of the RETURN value.
In the case of all other statements in routines, these pragmas have no effect.
Pragmas DATA TYPE, PREFETCH, UTILITY MODE
These pragmas are ignored when they precede a CALL statement or an SQL statement of a routine.