Routine (routine) 

The part of the CREATE DBPROC[EDURE] statement, CREATE TRIGGER statement, CREATE SYSTEM TRIGGER statement or CREATE FUNCTION statement described as the routine (routine) is the implementation of the database procedure, of the trigger or of the database function. It comprises optional variable declarations and statements.

Syntax

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

Control Structures, Statements and SQL Statements (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>
| <recursive_declare_cursor_statement>
| <select_statement>
| <single_select_statement>
| <subtrans_statement>
| <update_statement>

<create_table_temp> :: = <!  <create_table_statement> for creating temporary tables (in other words table name table_name) in the CREATE TABLE-statement must have the format TEMP.<identifier>   !>

<drop_table_temp> ::= DROP TABLETEMP.<identifier>

Examples

SQL Tutorial

Database Procedures

Database Functions

Database Triggers

Explanation

The statements concerning database procedures below also apply for triggers and database functions.

Variables

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, these variables can be used in any SQL or other statements.

Each database procedure contains 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 lines processed in an SQL statement is indicated by the $COUNT variable. For a QUERY statement, this variable can have the value -1, which means that an unknown number of records, but at least one, comprises the result of the QUERY statement. For a DELETE statement, this variable can have the value -1, which means that an unknown number of records has been deleted, possibly 0 records.

Variables can be assigned a value using the assignment_statement.

Control Structures

The IF statement  if_statement first evaluates the search condition. If this is met, 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 search condition specified is met. The condition is checked, in particular, before the statement is executed for the first time. This means the statement may not be executed at all. By specifying BREAK, you can exit the loop straight away without the condition being checked. If CONTINUE is specified in the loop, the condition is revaluated 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.

RETURN

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 when the <expression> function value is specified.

Troubleshooting

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 whenever the STOP function is invoked. The value of the first parameter of the STOP function is the return or error message received by the application as the result of the database procedure call. An error text can also be returned.

SQL Statements (routine_sql_statement)

Tables in SQL statements  routine_sql_statementof the database procedure must always be complete. In other words, the schema must be specified. For SELECT statements, a complete specification of the table name in the FROM clause is sufficient.

Restrictions

The statement list (statement_list) cannot contain more than 255 SQL statements.

The length of an SQL statement routine_sql_statement must not exceed approximately 8 KB.