In the SQL statements for creating and designing routines below, other SQL statements can also be used:
CREATE FUNCTION, CREATE PROCEDURE
CASE, COMPOUND (there also in exception routines), FOR, IF, LOOP, REPEAT, WHILE
Restrictions must be borne in mind for some of these statements.
To make these statements easier to read, the syntax element routine_sql_statement is described centrally here for these other SQL statements.
routine_sql_statement ::=
{
case_statement
for_statement
if_statement
iterate_statement
leave_statement
loop_statement
repeat_statement
set_statement
while_statement
return_statement
call_statement
single_row_select_statement
insert_statement
update_searched_statement
delete_searched_statement
merge_statement
open_statement
fetch_statement
update_positioned_statement
delete_positioned_statement
close_statement
get_diagnostics_statement
signal_statement
resignal_statement
}
routine_sql_statement
routine_sql_statement has a maximum length of 32000 characters.
The permitted SQL statements are presented in the following groups:
Control statements
case_statement
CASE statement which conditionally executes further SQL statements, see section "CASE - Execute SQL statements conditionally".
for_statement
FOR statement which executes further SQL statements in a loop, see section "FOR - Execute SQL statements in a loop".
if_statement
IF statement which conditionally executes further SQL statements. see section "IF - Execute SQL statements conditionally".
iterate_statement
ITERATE statement which switches to the next loop pass, see section "ITERATE - Switch to the next loop pass".
leave_statement
LEAVE statement which aborts loops or COMPOUND statements, see section "LEAVE - Terminate a loop or COMPOUND statement".
loop_statement
LOOP statement which executes further SQL statements in a loop, see section "LOOP - Execute SQL statements in a loop".
repeat_statement
REPEAT statement which executes further SQL statements in a loop, see section "REPEAT - Execute SQL statements in a loop".
set_statement
SET statement which assigns a value to a procedure parameter or a local procedure variable, see section "SET - Assign value".
while_statement
WHILE statement which executes further SQL statements in a loop, see section "WHILE - Execute SQL statements in a loop".
return_statement
RETURN statement which returns a return value for the UDF, see section "RETURN - Supply the return value of a User Defined Function (UDF)". This statement may not be used in procedures.
call_statement
CALL statement which another procedure calls, see section "CALL - Execute procedure".
The DEBUG ROUTINE and LOOP LIMIT pragmas have no effect ahead of a CALL statement in a procedure, see section "CALL - Execute procedure".Pragmas for optimization can also be specified in a procedure in the case of a CALL statement. They then have an effect on optimizing the call values.
SQL statements for querying and updating data without a cursor
single_row_select_statement
SELECT statement which reads a single row, see section "SELECT - Read individual rows".
insert_statement
INSERT statement which inserts rows into an existing table, see section "INSERT - Insert rows in table". This statement may not be used in UDFs.
update_searched_statement
UPDATE statement which updates the columns of the rows in a table which satisfy a particular search condition, see section "UPDATE - Update column values". This statement may not be used in UDFs.
delete_searched_statement
DELETE statement which deletes the rows in a table which satisfy a particular search condition, see section "DELETE - Delete rows". This statement may not be used in UDFs.
merge_statement
MERGE statement which, depending on a particular condition, updates rows in a table or inserts rows in a table, see section "MERGE - Insert rows in a table or update column values". This statement may not be used in UDFs.
SQL statements for querying and updating data with a cursor:
These statements are only permitted for a local cursor which is defined in a COMPOUND statement.
open_statement
OPEN statement which opens a cursor, see section "OPEN - Open cursor".
fetch_statement
FETCH statement which positions a cursor and possibly reads the current row, see section "FETCH - Position cursor and read row".
update_positioned_statement
UPDATE statement which updates the columns of the row in a table to which the cursor is positioned, see section "UPDATE - Update column values". This statement may not be used in UDFs.
delete_positioned_statement
DELETE statement which deletes the row in a table to which the cursor is positioned, see section "DELETE - Delete rows". This statement may not be used in UDFs.
close_statement
CLOSE statement which closes a cursor. see section "CLOSE - Close cursor" .
Diagnostic statements
get_diagnostics_statement
GET DIAGNOSTICS statement for outputting diagnostic information, see section "GET DIAGNOSTICS - Output diagnostic information".
signal_statement
SIGNAL statement which reports an error in the routine, see section "SIGNAL - Report exception in routine".
resignal_statement
RESIGNAL statement which reports an error in the exception routine, see section "RESIGNAL - Report exception in local exception routine".