The part of the CREATE DBPROC statement, CREATE TRIGGER statement, or CREATE FUNCTION statement described as the routine (routine) is the implementation of the database procedure, trigger, or database function. The routine comprises optional variable declarations and statements.
<routine> ::= [<local_variables>] <statement_list>;
<local_variables> ::= VAR
<local_variable_list>;
<local_variable_list> ::= <local_variable>
| <local_variable_list>; <local_variable>
<local_variable> ::= <variable_name> <data_type>
<variable_name> ::= <identifier>
<statement_list> ::= <statement> | <statement_list> ; <statement>
<statement> ::= BEGIN <statement_list>
END
| BREAK | CONTINUE | CONTINUE EXECUTE
| <if_statement>
| <while_statement>
| <assignment_statement>
| <case_statement>
| RETURN [<expression>]
| STOP (<expression> [,<expression>] )
| TRY <statement_list>; CATCH <statement>
| <routine_sql_statement>
<if_statement> ::= IF <search_condition>
THEN <statement> [ELSE <statement>]
<while_statement> ::= WHILE <search_condition> DO
<statement>
<assignment_statement> ::= [SET] <variable_name> =
<expression>
<case_statement> ::= <simple_case_statement>
| <searched_case_statement>
<routine_sql_statement> ::=
<call_statement>
| <close_statement>
| <create_table_temp>
| <declare_cursor_statement>
| <delete_statement>
| <drop_table_temp>
| <fetch_statement>
| <insert_statement>
| <lock_statement>
| <select_statement>
| <single_select_statement>
| <subtrans_statement>
| <update_statement>
<create_table_temp> :: = <create_table_statement> for creating temporary tables, that is, the table name (table_name) in the CREATE TABLE statement must have the format TEMP.<identifier>.
<drop_table_temp> ::= DROP TABLE TEMP.<identifier>
The statements below referring to database procedures are also true for triggers and database functions.
The local variables of the database procedure must be declared explicitly by specifying a data type before they are used. Only BOOLEAN, CHAR[ACTER], DATE, FIXED, FLOAT, INT[EGER], NUMBER, REAL, SMALLINT, TIME, TIMESTAMP, and VARCHAR are permitted as data types. Once they have been declared, the variables can be used in any SQL and other statements.
Every database procedure has the variables $RC, $ERRMSG, and $COUNT implicitly.
The $RC
variable returns a numeric error code after an SQL statement has been
executed. The value 0 means that
the SQL statement was successfully executed.
In parallel with $RC, the
$ERRMSG variable returns an explanation of the error containing a
maximum of 80 characters.
The number of rows processed in
an SQL statement is indicated by the $COUNT variable.
Variables can be assigned a value with the statement assignment_statement.
The IF statement (if_statement) first evaluates the search condition. If this is fulfilled, the statement specified in the THEN branch is executed. Otherwise, the statement in the ELSE branch (if defined) is executed.
The WHILE statement (while_statement) enables statements to be repeated in response to certain conditions. The statement is executed as long as the specified search condition is true. The condition is checked, in particular, before the statement is executed for the first time. This means that the statement may not be executed at all. By specifying BREAK, you can exit the loop straight away, without checking the condition. If CONTINUE is specified in the loop, the condition is re-evaluated immediately and the loop is processed again or exited, depending on the result.
The CASE statement (case_statement) allows the conditional execution of a statement, dependent on search conditions or the equality of operands. There are simple and general CASE statements.
CREATE DBPROC and CREATE TRIGGER statement: Specifying RETURN enables you to exit the surrounding database procedure immediately without causing errors.
CREATE FUNCTION statement: Specifying RETURN <expression> enables you to exit the database function while specifying the function value <expression> at the same time.
If an SQL error occurs in the statement list between TRY and CATCH, the system branches directly to the statement that follows CATCH. The actual troubleshooting routine can be programmed in this statement. If CONTINUE EXECUTE is executed here, the system jumps directly to the point after the statement that triggered the error.
The database procedure is interrupted immediately when the STOP function is invoked. The value of the first parameter of the STOP function is the return or error message that the application receives as the result of the database procedure call. An error text can also be returned.
The tables in the SQL statements (routine_sql_statement) of the database procedure must always be complete, that is, with the schema specified. In the case of SELECT statements, a full statement of the table name in the FROM clause is sufficient.
The statement list (statement_list) must not contain more than 255 SQL statements.
The length of an SQL statement (routine_sql_statement) must not exceed approximately 8 KB.