Routine 

The part of the CREATE DBPROC or CREATE TRIGGER-statement referred to as the routine is the implementation of the database procedure or trigger. 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>

identifier, data type, statement

Explanation

Variables

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 lines processed in an SQL statement is indicated by the $COUNT variable.

Variables can be assigned a value with the assignment statement (see statement).

Restrictions

The statement list must not contain more than 255 SQL statements.